Moti Moti - 1 month ago 5
MySQL Question

Mysql query for multiple values for the same id

I hope someone can help me with a mysql statement. In the end I need it this way for example: (will be json encoded later).

{"meta_value": "Breitensteinstr 82031"} //street and plz


{"meta_value": "Danziger Str 65307"} //street and plz


{"meta_value": "71032"} //no street on this one


I can get the single values by doing

SELECT meta_value FROM `stroma_commentmeta` WHERE meta_key = 'plz' or meta_key = 'street';


but now I'm stuck.

How can I get both values for the same comment_id in the same table? My brain can't get the connections.

Screen of the DB:

enter image description here

Answer
 SELECT  group_concat( 
      meta_value 
         ORDER BY  
         meta_key DESC  
         SEPARATOR " "  ) as meta_value 
  FROM `stroma_commentmeta` 
  WHERE meta_key = 'plz' or meta_key = 'street' 
  GROUP BY comment_id ;

this comes with some limitations :

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

alternatively just join them together ...

SELECT  straight_join concat( s.meta_value, " " , p.meta_value  ) as    meta_value  
FROM `stroma_commentmeta` p
JOIN `stroma_commentmeta` s on s.comment_id=p.comment_id and s.meta_key = 'street'
  WHERE p.meta_key = 'plz' ;