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.

Example:

ticket
    ticket_id: 1

    ticket_id: 2

    ticket_id: 3


attr
    ticket_id: 1
    attr_type: 1
    attr_val: Foo

    ticket_id: 1
    attr_type: 2
    attr_val: Bar

    ticket_id: 1
    attr_type: 3
    attr_val: Egg

    ticket_id: 2
    attr_type: 2
    attr_val: Spam

the result should be:

ticked_id: 1
attr_val1: Foo
attr_val2: Bar
attr_val3: Egg

ticked_id: 2
attr_val1: NULL
attr_val2: Spam
attr_val3: NULL

ticked_id: 3
attr_val1: NULL
attr_val2: NULL
attr_val3: NULL

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

Solution 1

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.

Solution 2

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

Solution 3

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

Comments

[rahim.nagori]

I tried your query, in my similar problem but it is returning same record. Why is that? Where I'm mistaking?

[Alan Stewart]

This worked best for me because I had to do an INNER JOIN on tables I wanted to LEFT JOIN.