SQL LEFT JOIN WHERE

Question

I have two tables, one with settingIDs and values of those settings for each userID and another with userIDs, their emails and user names.

I am trying to join the values of specific settings, the problem is that not all users have this specific setting tied to their ID, so I end up with less rows than I actually need.

Table 1
userID settingID settingValue

Table 2
userID userDOB userEmail userName

My query looks like this:

SELECT u.userID, u.userEmail, s.settingValue
FROM users u
LEFT JOIN userSettings s ON u.userID = s.userID
WHERE s.settingID = 1

What do I need to do to get all of the users in the list?

Possible duplicate of [Left Join With Where Clause](http://stackoverflow.com/questions/4752455/left-join-with-where-clause) — Clockwork-Muse, Aug 07 '16 at 19:53

score 3 · Accepted Answer · answered Aug 07 '16 at 19:50

3

Your where clause turns you left join into an inner join. Put the condition in the JOIN

SELECT u.userID, u.userEmail, s.settingValue
FROM users u
LEFT JOIN userSettings s ON u.userID = s.userID
                        AND s.settingID = 1

answered Aug 07 '16 at 19:50

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Aug 07 '16 at 20:27

you can use UNION ALL clause to combine two JOINS

SELECT  u.userID, u.userEmail, s.settingValue
     FROM users u
     LEFT userSettings s
     ON u.userID = s.userID
UNION ALL
     SELECT  u.userID, u.userEmail, s.settingValue
     FROM users u
     RIGHT userSettings s
     ON u.userID = s.userID

SQL LEFT JOIN WHERE

2 Answers2