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

Answer 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.