SQL connects on a one-to-many basis, where none of the many matches the given value

Question

Let's say I have two tables

User ----- id first_name last_name User_Prefs ----- user_id pref 

Examples of data in User_Prefs may be

 user_id | pref 2 | SMS_NOTIFICATION 2 | EMAIL_OPT_OUT 2 | PINK_BACKGROUND_ON_FRIDAYS 

And some users may not have the corresponding lines in User_Prefs.

I need to request the first and last name of any user who does NOT have EMAIL_OPT_OUT as one of their (possibly many, perhaps not) User_Pref lines.

 SELECT DISTINCT u.* from User u LEFT JOIN User_Prefs up ON (u.id=up.user_id) WHERE up.pref<>'EMAIL_OPT_OUT' 

gets me all who have at least one line that is not "EMAIL_OPT_OUT", which, of course, is not what I want. I want everything without strings that match "EMAIL_OPT_OUT".

Is there a way for the join type and join conditions to filter out the rows that I want to leave here? Or do I need a subquery?

Answer 1

I personally think that the type of sentence "where does not exist" may be easier to read, but here is a query with a join that does the same.

 select distinct u.* from User u left join User_Prefs up ON u.id = up.user_id and up.pref = 'EMAIL_OPT_OUT' where up.user_id is null 

Answer 2

Why wouldn’t your user settings be saved in the user table as logical fields? This will greatly simplify your queries.

 SELECT * FROM User WHERE EMAIL_OPT_OUT = false