0

I have a table wmeta of meta data for items: (some items have missing data) Simplified example: 

 id | item_id | meta_key    | meta_value
 1  |100      |first_name   |Joe
 2  |100      |last_name    |Bloggs
 3  |100      |age          |21
 4  |101      |first_name   |Fred
 5  |101      |last_name    |Smith
 6  |102      |first_name   |Jane
 7  |102      |last_name    |Doe
 8  |102      |age          |22

If I have another table wfields with all the keys

id |meta_name
 1 |first_name
 2 |last_name
 3 |age

using the query below I am not getting the null I expected for the missing age record.

SELECT wf.meta_name, wm.item-id, wm.meta_value 
FROM wfields as wf 
LEFT JOIN wmeta as wm 
ON wf.meta_name = wm.meta_key

The output I want is for a table display/export to csv

100 | Joe Bloggs 22  
101 | Fred Smith ''  
102 | Jane Doe   21
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
ChrisH
  • 127
  • 1
  • 11
  • It's better if you give DDL queries to use in something like http://sqlfiddle.com/ – Battle_Slug Sep 26 '17 at 17:49
  • 2
    You only get a null if there are no matches at all for the joining condition. There are several rows with `meta_key = age`, so the condition is satisfied. – Barmar Sep 26 '17 at 17:50
  • @Battle_Slug, ok thanks. I didn't know about this, but will in future – ChrisH Sep 26 '17 at 17:52
  • @Barmar thanks for explanation of why it doesn't work. Any suggestion how I should change it so it gives the output I want? – ChrisH Sep 26 '17 at 17:53

3 Answers3

1

for obtain the result of each item_id on the same rows you should use group_concat (the join without aggreagtion return the values on several rows)

SELECT item_id, 
GROUP_CONCAT( meta_value ORDER BY field(meta_key, 'first_name', 'last_name', 'age' ) SEPARATOR ' ')
FROM wfields as wf 
LEFT JOIN wmeta as wm ON wf.meta_name = wm.meta_key
GROUP BY item_id
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Yes, that works, I will try it on the "real" tables. How would query look if I wanted as separate records? Just removing `GROUP_CONCAT` obviously does not work. Happy to get null as value for missing records – ChrisH Sep 26 '17 at 18:46
  • @ChrisH could be you need a self join with the table fo 3 times but I can not understand what your new goal is .. would be better if you post a new well documented question with a clear example of the result you want to get .. possibly when you post the new question comment me the link – ScaisEdge Sep 26 '17 at 18:50
1

You just need to pivot your table:

SELECT item_id,
        MAX(IF(meta_key = 'first_name', meta_value, '')) AS first_name,
        MAX(IF(meta_key = 'last_name', meta_value, '')) AS last_name,
        MAX(IF(meta_key = 'age', meta_value + 0, NULL)) AS age
FROM wmeta
GROUP BY item_id

If you need to get the columns dynamically, see the answers here: MySQL pivot table

You can also cross join the list of meta keys and item IDs first, then left join that with the wmeta table:

SELECT x.item_id, x.meta_name, m.meta_value
FROM (SELECT DISTINCT item_id, meta_name
      FROM wmeta
      CROSS JOIN wfields) AS x
LEFT JOIN wmeta AS m
ON m.item_id = x.item_id AND m.meta_key and x.meta_name
ORDER BY x.item_id, x.meta_name

This will get all the null values, but each attribute will be on a separate row.

Barmar
  • 741,623
  • 53
  • 500
  • 612
0

What you are looking for is a FULL OUTER JOIN that JOIN all records of one table with another table, whether there is a link or not.

Jacques Amar
  • 1,803
  • 1
  • 10
  • 12