MySQL selecting missing rows

Question

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

@nico Is this stackoverflow.com/questions/19335787/… any help? — rlb.usa
– rlb.usa, Commented Sep 21, 2015 at 23:11
@JuanRuizdeCastilla but I want the rows where meta_value is NULL, that would exclude them, won't it? — nico
– nico, Commented Sep 21, 2015 at 23:14
@rlb.usa sort of... trying to use CASE but I must be missing something... — nico
– nico, Commented Sep 21, 2015 at 23:14
@nico I feel like you could get better faster help if you used something like sqlfiddle — rlb.usa
– rlb.usa, Commented Sep 21, 2015 at 23:19

Juan Ruiz de Castilla · Accepted Answer · 2015-09-22 03:15:40Z

2

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

edited Sep 22, 2015 at 3:15

answered Sep 21, 2015 at 23:30

Juan Ruiz de Castilla

99215 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rlb.usa Over a year ago

This is another possibility but it has a O(n^2) runtime which might be expensive - or not! Really depends on how much data OP has and how fast it needs to be.

nico Over a year ago

I'm going to try this on my real data and let you know

Juan Ruiz de Castilla Over a year ago

@rlb.usa, I am not sure, if this second option is faster with more data, can you please check this.

rlb.usa Over a year ago

@JuanRuizdeCastilla Yes it is going to be faster, it is an O(n) runtime as it does not need a new query for each row. : )

nico Over a year ago

The second option is indeed 30+ times faster on my real DB!

Collectives™ on Stack Overflow

MySQL selecting missing rows

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related