Chud37 Chud37 - 2 years ago 66
SQL Question

SQL Joins - Gather all from RIGHT table

I have two tables:


id | userID | keyID | val
1 99 1 1


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
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
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

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download