Chud37 Chud37 - 7 months ago 15
SQL Question

SQL Joins - Gather all from RIGHT table

I have two tables:

user-data
:

id | userID | keyID | val
1 99 1 1


user-data-keys


id | key
1 is-staff
2 description
3 image


Now, when I run the following SQL, I get the desired output:

SELECT `key`,`val` FROM `user-data` RIGHT JOIN `user-data-keys` ON `user-data`.`keyID` = `user-data-keys`.`id`;


Which produces:

key | val
is-staff 1
description NULL
image NULL


Which is exactly what I want. However when I add a WHERE clause to the SQL:

SELECT `key`,`val` FROM `user-data` RIGHT JOIN `user-data-keys` ON `user-data`.`keyID` = `user-data-keys`.`id` WHERE `userID` = 99;


I only get the one row with
is-staff
in it. Which I understand, as I asked for only rows with
userID = 99
. However I am planning on storing lots of different user's information in the one
user-data
table, and I want to know if they have a NULL value for each of the keys. So how can I achieve this? I know it's got to be some kind of fancy join that I am not aware of.

So to clarify: i need the output like this:

key | val
is-staff 1
description NULL
image NULL


When using a
WHERE userID = 99
. Currently I only get one row whilst using a
WHERE
clause.

Answer

Move the predicate from the WHERE clause to the join:

SELECT `key`,`val` FROM `user-data` RIGHT JOIN `user-data-keys` ON `user-data`.`keyID` = `user-data-keys`.`id` and `userID` = 99;