CODEWITHSUNDEEP
mysqlselect
nico
nico

Reputation: 51640

MySQL selecting missing rows

I have a table with user info:

    user_id | meta_key | meta_value
 -----------+----------+-------------
      1     | name     | John
      1     | surname  | Doe
      2     | name     | Luke
      3     | name     | Jane

I want to select rows with a certain key, given a list of IDs.

If I do:

SELECT meta_value FROM table WHERE meta_key = 'surname' AND user_id IN(1,2,3)

MySQL will only return the info for user 1, since the others do not have surname.

I would like to build a query to return exactly the number of rows as the IDs passed, with NULL (or an empty string) if that particular user has no surname in the table.

I have tried to use IFNULL(meta_value, "") and also using IF, but it does not seem to help.

Any suggestions? I am sure it's a trivial thing but I can't seem to get around this issue.

Here's a SQLfiddle showing the issue: http://sqlfiddle.com/#!9/86eef2/6

My expected output would be:

Doe
NULL
NULL

Upvotes: 2

Views: 570

Answers (1)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

Try this query:

SELECT DISTINCT user_id,
(SELECT meta_value FROM mytable B WHERE B.user_id = mytable.user_id AND META_KEY = 'surname') AS 'surname_meta_value'
FROM mytable
WHERE user_id IN(1,2,3)

For study purpose, this could be a faster option, in most cases, based on rlb.usa solution:

SELECT user_id, 
GROUP_CONCAT(
(CASE WHEN meta_key = "surname" 
    THEN meta_value
    ELSE '' 
END) SEPARATOR '')
AS 'surname_meta_value'
FROM mytable WHERE user_id IN(1,2,3)
GROUP BY user_id

Upvotes: 2

Related Questions