CODEWITHSUNDEEP
mysql
Chud37
Chud37

Reputation: 5027

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.

Upvotes: 1

Views: 37

Answers (2)

Brijal Savaliya
Brijal Savaliya

Reputation: 1091

If you want to get rows which are not matched then you can add where conditions with join like " ON user-data.keyID = user-data-keys.id AND userID = 99 " instead of " WHERE userID = 99 ", 

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

Upvotes: 0

Kane Armstrong
Kane Armstrong

Reputation: 106

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;

Upvotes: 4

Related Questions