aozora aozora - 1 year ago 97
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 Source

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:

       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 =

Demo here:


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download