Search code examples
mysqldistinct

SQL get distinct values from same table twice based on first value

I have a metadata table with a meta_key and meta_value, the meta keys are among other things the country_iso and country. The iso value always exist, but the country value does not. The code below grabs all the unique iso values, but I want to get the matching country names as well.

SELECT DISTINCT pm.meta_value 
FROM postmeta pm 
    LEFT JOIN posts p ON p.ID = pm.post_id 
WHERE pm.meta_key = 'country_iso' 
AND pm.meta_value > '' 
AND p.post_type = 'locations' 
AND p.post_status 
NOT IN ( 'trash', 'auto-draft' ) 
ORDER BY pm.meta_value ASC;

I tried to use IN in combination with country_iso and country, but this shows them all in the same row, which is also not what I'm looking for. In the end I want to get the results in two rows with the unique country iso and country names.

I'm sure it can be one in one query, but it's giving me a headache so far. Anyone who can point me in the right direction?

Solution

  • You should use post as main table and join postmete with inner join for iso and left join for country

        SELECT DISTINCT pm1.meta_value, pm2.meta_value
    FROM  posts p
    INNER JOIN postmeta pm1 ON p.ID = pm1.post_id  ON pm1.meta_key = 'country_iso' AND pm1.meta_value > '' 
    LEFT JOIN postmeta pm2 ON p.ID = pm2.post_id AND pm2.meta_key = 'country' 
    WHERE p.post_type = 'locations' 
    AND p.post_status NOT IN ( 'trash', 'auto-draft' ) 
    ORDER BY pm1.meta_value, pm2.meta_value ;