#mysql MySQL query snippets

mysql.md

Insert with select

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2  

Show key value row based (e.g., meta) table as columns with main table

Example: if you have users(id, email) and user_meta(user_id, key, value) tables where you have phone, age etc keys in user_meta but need to show these as columns with users table

SELECT 
u.id, u.email, 
GROUP_CONCAT(IF(um.meta_key='phone', um.meta_value, NULL)) AS phone, /* NULL so that other columns do not print anything */
GROUP_CONCAT(IF(um.meta_key='verified', IF(um.meta_value=1, 'Yes', 'No'), NULL)) AS verified
FROM users u
JOIN user_metas um ON u.id=um.user_id
GROUP BY u.id