Many-Many Relationship Searching Problem

Yii 1.1 General Discussion
1

I have 3 tables User, Skill, UserSkill:


User: id

Skill: id

UserSkill: id, user_id, skill_id

The relationship as following: User - HASMANY UserSkill, Skill HASMANY UserSkill and User MANY-MANY Skill. The problem is when I adding Skill to the User, I have to list all the Skill that available (not been selected). But I did not find the good condition for the search when fetching from the database that could list only available skill. For example, I updating the User id = 2, dropdownlist will show all the skill : s1,s2,s3. I selected s1 then save new row for UserSkill. Then when I update User again, I would like to see the dropdownlist has only 2 skill: s2,s3 only.

Here is the UserSkill table after saved, occurs the duplicated row:


 ID: 2	

User: 1

Skill 3

ID: 3	

User: 2

Skill 3

ID: 10	

User: 2

Skill: 3
2

You could query Skill using not in condition:




"SELECT * FROM skill WHERE id NOT IN (SELECT skill_id FROM user_skill WHERE user_id = ".$userId.")"
3

Thank u, it help a lot :D.