aozora aozora - 2 months ago 6
MySQL Question

MySQL one-to-many relationship single query

I have this two tables. I'll make it as simple as possible:

Sample value in main_table:
id = 1

Sample value in details_table:
id = 1
type = book
name = harry potter

id = 1
type = paper
name = post it


The result I need is to get id with the name for those two types.

id book paper
1 harry potter post it


Is this even possible?

Answer

Your expected output suggests that you want to pivot the type and generate columns based on its value. Assuming the only types which can appear are book and paper, then the following query should work:

SELECT t2.id,
       MAX(CASE WHEN t2.type = 'book'  THEN name ELSE NULL END) AS book,
       MAX(CASE WHEN t2.type = 'paper' THEN name ELSE NULL END) AS paper
FROM main_table t1
INNER JOIN details_table t2
    ON t1.id = t2.id
GROUP BY t2.id

Demo here:

SQLFiddle