Sparkman Sparkman - 4 months ago 19
MySQL Question

SQL LEFT JOIN WHERE

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?

Answer

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