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!

解决方案

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')