MySQL: How do I join same table multiple times?

Question

I have two tables ticket and attr. Table ticket has ticked_id field and several other fields. Table attr has 3 fields:

ticket_id - numeric
attr_type - numeric
attr_val - string

attr_type is a fixed enum of values. For example, it can be 1, 2 or 3.

I need to make a query, the result of which will be 4 columns:

ticket_id, attr_val for attr_type=1, attr_val for attr_type=2, attr_val for attr_type=3

If there is no corresponding value for attr_type in attr table then NULL value should be shown in corresponding column.

ticket table :

ticket_id
1
2
3

attr table :

ticket_id	attr_type	attr_val
1	1	Foo
1	2	Bar
1	3	Egg
2	2	Spam

Expected output :

ticket_id	attr_val1	attr_val2	attr_val3
1	Foo	Bar	Egg
2	null	Spam	null
3	null	null	null

I tried left joining attr table 3 times, but cannot figure out how to arrange output by attr_type

score 83 · Accepted Answer · answered Sep 24 '12 at 09:15

83

You need to use multiple LEFT JOINs:

SELECT 
    ticket.ticket_id,  
    a1.attr_val AS attr_val1,
    a2.attr_val AS attr_val2,
    a3.attr_val AS attr_val3
FROM ticket
    LEFT JOIN attr a1 ON ticket.ticket_id=a1.ticket_id AND a1.attr_type=1
    LEFT JOIN attr a2 ON ticket.ticket_id=a2.ticket_id AND a2.attr_type=2
    LEFT JOIN attr a3 ON ticket.ticket_id=a3.ticket_id AND a3.attr_type=3

Here is an example: SQL Fiddle.

answered Sep 24 '12 at 09:15

András Ottó

7,605
1
28
38

I tried your query, in my similar problem but it is returning same record. Why is that? Where I'm mistaking? – rahim.nagori May 26 '20 at 13:03

score 13 · Answer 2 · answered Sep 24 '12 at 09:23

While you can use aliased left joins, in this case you could also use a combination of grouping and conditional expressions:

select t.ticket_id,
       max(case when a.attr_type=1 then a.attr_val end) attr_val1,
       max(case when a.attr_type=2 then a.attr_val end) attr_val2,
       max(case when a.attr_type=3 then a.attr_val end) attr_val3
from ticket t
left join attr a on t.ticket_id = a.ticket_id
group by t.ticket_id

podiluska · Answer 3 · 2012-09-24T09:04:12.477

11

You use table aliases

eg:

Select 
    ticket.ticket_id,  
    a1.attr_val as attr_val1,
    a2.attr_val as attr_val2,
    a3.attr_val as attr_val3
from ticket
    left join (select * from attr where attr_type=1) a1 on ticket.ticket_id=a1.ticket_id
    left join (select * from attr where attr_type=2) a2 on ticket.ticket_id=a2.ticket_id
    left join (select * from attr where attr_type=3) a3 on ticket.ticket_id=a3.ticket_id

edited Sep 24 '12 at 09:04

answered Sep 24 '12 at 08:58

podiluska

50,950
7
98
104

This worked best for me because I had to do an INNER JOIN on tables I wanted to LEFT JOIN. – Alan Stewart May 12 '21 at 01:08

MySQL: How do I join same table multiple times?

3 Answers3

Linked

Related