Joining same table with NULL values in MySQL

Question

I have a simple table like

user_id | meta_key | meta_value
===============================
   1    | color    | green
   2    | color    | red
   1    | size     | L
   2    | size     | L
   3    | color    | blue

...

Now I like a single query where I get all colors and sizes of a certain user

SELECT a.user_id AS ID, a.meta_value AS color, b.meta_value AS size
   FROM table AS a
   LEFT JOIN table AS b ON a.user_id = b.user_id 
WHERE a.meta_key = 'color' AND b.meta_key = 'size' GROUP BY a.meta_value, b.meta_value

This works as long I have a size for each user. If size is missing (ID 3 in example) the user isn't in the result

spencer7593 · Accepted Answer · 2014-06-16 14:05:30Z

2

The predicate on b.meta_key in the WHERE is negating the "outerness" of the LEFT JOIN operation.

One option is to relocate that predicate to the ON clause, for example:

SELECT a.user_id AS ID, a.meta_value AS color, b.meta_value AS size
   FROM table AS a
   LEFT JOIN table AS b ON a.user_id = b.user_id 
      AND b.meta_key = 'size'
 WHERE a.meta_key = 'color' GROUP BY a.meta_value, b.meta_value

FOLLOWUP

Q: What if color is missing?

A: You'd need a table or inline view as a rowsource that returns every value of user_id that you want included in the result set. For example, if you had a table named "users" with one row per user_id, and you want to return every id in that table:

 SELECT u.id         AS user_id
      , a.meta_value AS color
      , b.meta_value AS size
   FROM user u
   LEFT
   JOIN `table` a
     ON a.user_id = u.id
    AND a.meta_key = 'color'
   LEFT
   JOIN `table` b
     ON b.user_id = u.id
    AND b.meta_key = 'size'
  GROUP
     BY u.id
      , a.meta_value
      , b.meta_value

We also note that the GROUP BY in your original query does not include the user_id column, so any rows that have the same meta_value for color and size will be collapsed, and you'll get only one of the user_id values that have matching color and size.

Note that if the combination of user_id and meta_key is UNIQUE (in table table), then the GROUP BY isn't necessary.

In the normative relational model, attributes of an entity are implemented as columns. As a good example, the resultset being returned by the query looks a lot like the table we'd expect.

When an Entity Attribute Value (EAV) model is implemented in a relational database, the SQL gets an order of magnitude more complicated.

If you want to return ONLY values of user_id that have either color or size, you could get a list of user_id values with an inline view (query against table). This query is the same as above, but replacing the reference to the user table with an inline view:

 SELECT u.id         AS user_id
      , a.meta_value AS color
      , b.meta_value AS size
   FROM ( SELECT t.user_id AS id
            FROM table t
           WHERE t.meta_key IN ('color','size')
          GROUP BY t.user_id
        ) u
   LEFT
   JOIN `table` a
     ON a.user_id = u.id
    AND a.meta_key = 'color'
   LEFT
   JOIN `table` b
     ON b.user_id = u.id
    AND b.meta_key = 'size'
  GROUP
     BY u.id
      , a.meta_value
      , b.meta_value

edited Jun 16, 2014 at 14:05

answered Jun 16, 2014 at 13:28

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

2 Comments

spencer7593 Over a year ago

@revaxarts: you'd need a "source" for the user_id column, either a table or inline view that is guaranteed to return every value of user_id you want returned. I'll update my answer with a followup.

Xaver Over a year ago

Thanks for the answer. I thought about the problem when either color nor size exits. In my case I wouldn't need that entry at all so the first answer is right

Andrew · Accepted Answer · 2014-06-16 13:29:39Z

1

The where clause is killing you. Let's take b.meta_key = 'size'. If there are no rows in your B table, meta_key will come back null. The filter on that column removes them from the result. Try adding the b.meta_key = 'size' to the on clause.

answered Jun 16, 2014 at 13:29

Andrew

8,8933 gold badges31 silver badges54 bronze badges

Comments

Jey · Accepted Answer · 2014-06-16 13:31:43Z

0

try following code,

SELECT a.user_id AS ID, a.meta_value AS color, b.meta_value AS size FROM table AS a where a.meta_key = 'color' or a.meta_key = 'size'

answered Jun 16, 2014 at 13:31

Jey

607 bronze badges

Comments

Hituptony · Accepted Answer · 2014-06-16 13:37:32Z

0

SELECT a.user_id AS ID,
a.meta_value AS color, b.meta_value AS size
FROM table AS a
LEFT JOIN table AS b ON a.user_id = b.user_id 
WHERE a.meta_key in ('Color', 'Size')

answered Jun 16, 2014 at 13:37

Hituptony

2,8703 gold badges27 silver badges44 bronze badges

Collectives™ on Stack Overflow

Joining same table with NULL values in MySQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related