Help with select needed

[BennySHS]

hey there,

I want to do the following, but can't write the correct statement:

Tables:

tbl_user
user_id | username

tbl_group
group_id | groupname

tbl_useringroup
user_id | group_id


All I want is to select all tbl_group.groupname where the user is not in. (the goal is to show a selectbox with only the groups the user is not assinged)

I tried the following, but its sh*t
SELECT DISTINCT tbl_group.group_id, tbl_group.groupname FROM tbl_group, tbl_useringroup
WHERE tbl_useringroup.gruppenid <> tbl_group.group_id
AND tbl_useringroup.userid = '$userid'

hope you can help me,
thx && greets, ben

 

[towerbase]

Assuming your version of MySQL supports subselects then something like this should work

SELECT groupname FROM tbl_useringroup ug, tbl_group g
WHERE ug.group_id NOT IN (SELECT ug.group_id FROM ug WHERE user_id='$userid') 
AND ug.group_id=g.group_id

Andrew
Hampshire, UK

 

[BennySHS]

hi Andrew,

thx a lot for your help, with a "distinct" it works fine!
greets,
ben