Looking for sql to find a list of people in a database who don't have a particular row without using not exists.

Question

It's easy to find all the users ids who have trait.color = "green" but I need to find all the people who don't. The obvious way is a subselect for all the ids where not exists (select id where trait.color = "green') but I was trying to think if there's a way to do it without a subselect. Is there some trick I don't know about?

sybase 12.5 No foreign keys, but the table is a one to many relationship. Just imagine a simple

ID   trait
--   -----
1    yellow
1    green
1    blue
2    yellow
2    blue
2    black
3    yellow
3    green
3    black

Answer 1

Join on the UserID where trait.color <> "green"?

Answer 2

NOT EXISTS is most efficient on SQL Server... just do it :-)

...MyTable M where not exists (
   select id FROM trait T where T.color = 'green' AND T.key = M.key)

Answer 3

If the User table has a one-to-many relationship with the Traits table then the NOT EXISTS is most likely your best answer.

You can also try a distinct clause with a left join and trait.userid = NULL

SELECT Distinct Users.UserId
FROM USERS
    LEFT JOIN (SELECT * FROM Traits Where Color = 'GREEN') GreenTraits
        ON Users.UserId = GreenTraits.UserId
WHERE 1=1
AND GreenTraits.UserId = NULL