[SOLVED] Grouping MySQL

[ionik]

I dont understand why this isnt working here is the sql statement

 

SELECT u.username, u.userid FROM users u, roster r WHERE u.clan_member = 1 AND u.userid <> r.userid AND catid = 1 GROUP BY u.clan_member ORDER BY u.username DESC

 

what i want this to do is select all users that have clan_member = 1, but are not allready contained in the roster table....

 

table is as follows

 

users

-------------

- username STRING

- userid PRIMARY KEY

- clan_member INT

 

roster

--------------

-userid INT

-catid INT links to roster catergories

[mwasif]

Use LEFT JOIN

SELECT u.username, u.userid FROM users u
LEFT JOIN roster r ON r.userid = u.userid
WHERE u.clan_member = 1 AND r.userid IS NULL 
AND catid = 1 GROUP BY u.clan_member ORDER BY u.username DESC

[ionik]

that to is not working what happens is if there are any rows matching roster table userid and user table userid it will only return one result and its the first result it comes to

[fenway]

that to is not working what happens is if there are any rows matching roster table userid and user table userid it will only return one result and its the first result it comes to

Huh?

[ionik]

ok...lol

 

what i need this to do is

 

Select username and userid from userid where clan_member = 1

they are not in the roster table which would be the userid for the roster table matches userid from user table and catid = 1

[fenway]

Which table is catid in?