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

Clockwork-Muse
– Clockwork-Muse

2016-08-07 19:53:16 +00:00
Commented Aug 7, 2016 at 19:53 — Clockwork-Muse
– Clockwork-Muse, Commented Aug 7, 2016 at 19:53

juergen d · Accepted Answer · 2016-08-07 19:50:12Z

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 7, 2016 at 19:50

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

R.K123 · Accepted Answer · 2016-08-07 20:27:36Z

0

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

answered Aug 7, 2016 at 20:27

R.K123

1592 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL LEFT JOIN WHERE

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related