MySQL 'WHERE' clause which excludes result

Question

I have the following SQL query:

SELECT members.member_ID, members.nick_name 
          FROM orgs
    INNER JOIN assets ON assets.org_ID = orgs.org_ID
    INNER JOIN orgs_to_members ON orgs_to_members.org_ID = orgs.org_ID
    INNER JOIN members ON members.member_ID = orgs_to_members.member_ID
    where orgs.org_ID = '7' 
    AND NOT EXISTS (select shares.member_ID from shares where shares.asset_ID = '224')

There are 3 members in org 7:

     - member_ID 1
     - member_ID 4
     - member_ID 6

In the subquery, member IDs 1 and 4 result. I am trying to write 1 query which will return only member ID #6. When i run the above query, I get no results. When I separate them, I get the expected results. Please help.

Thanks!

Answer 1

AND NOT EXISTS (select ...) is used to make sure that the subquery doesn't return any rows. It usually only makes sense if the subquery is correlated (i.e., if it refers to values from the outer query), since otherwise it will either be true for every result row (and won't actually affect the query), or be false for every result row (and will cause the query to return no results at all, as in your case). I think what you want is:

    AND members.member_ID NOT IN (select shares.member_ID from shares where shares.asset_ID = '224')