CODEWITHSUNDEEP
mysqlleft-join
ChrisH
ChrisH

Reputation: 127

MySQL Left Outer Join Issue

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

Upvotes: 0

Views: 41

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Barmar
Barmar

Reputation: 780949

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.

Upvotes: 1

Jacques Amar
Jacques Amar

Reputation: 1833

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.

Upvotes: 0

Related Questions