CODEWITHSUNDEEP
mysql
aozora
aozora

Reputation: 421

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?

Upvotes: 0

Views: 43

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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

Upvotes: 1

Jens
Jens

Reputation: 69440

Simply join the details table twice:

Select m.id, d1.name as book, d2.name as paper from main_table m 
join Details_table d1 on m.id =d1.id and d1.type = 'book'
join Details_table d2 on m.id =d2.id and d2.type = 'paper'

Upvotes: 2

Related Questions