Join Problem

[ziycon]

I'm trying to do the below join, i have two tables as below. What i need is to get a list of all members from the 'members' table that have active set to 1 excluding any members in the admin_privilege s table??


members:
id(PK),uname,ac tive

admin_privilege s:
id(PK),uid(FK-members.id)

SELECT members.id,members.uname,admin_pivileges.uid FROM members, admin_privileges WHERE members.active='1' AND members.id != admin_pivileges.uid

[ziycon]

There was one or two typo's with the previous query, this on is the one I'm using.

SELECT members.id,members.uname,admin_privileges.uid FROM members, admin_privileges WHERE admin_privileges.uid != members.id AND active = '1'

[Atli]

Hi.

Joining tables using a comma, like you do, is rarely the best way to join tables. It simply joins all rows in table A with all rows in table B, usually creating a lot more rows then are wanted or needed.
This is why your query fails, because you do not account for all the rows the condition-free join is creating, and assume the tables will only be joined on the PK-FK columns.

You should choose a more appropriate JOIN type when joining tables, and use the ON clause to condition the merger of the tables.
Check out 12.2.8.1. JOIN Syntax in the manual for full details on that.

In your case, however, a JOIN may not be your best option.
A simple sub-query might be more appropriate here.

For example:
[code=mysql]
SELECT `id`
FROM `members`
WHERE `active` = 1
AND ( SELECT TRUE
FROM `admin_privileg es` WHERE `uid` = m.`id`
) IS NULL;[/code]

[ziycon]

Thanks for the help with that, i got a sub query to work for me!