Menu
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
216
As you may know, it is used to join and combine data from two or more tables into one common data set. In this article, I'm going to discuss special types of joins? in which you combine the same table twice—including joining a table to itself, also known as the self join.
To self join the same table multiple times you can use the from parameter, as mentioned in the docs, to change the "join" name of the table. In this example, we join in person to order twice, with different names and different joining dimensions.
You use a single table twice in a query by giving it two names, like that. The aliases are often introduced with the keyword AS. You also normally specify a join condition (for without it, you get the Cartesian Product of the table joined with itself). For preference you use the explicit JOIN notation.
The self-join is a special kind of joins that allow you to join a table to itself using either LEFT JOIN or INNER JOIN clause. You use self-join to create a result set that joins the rows with the other rows within the same table.
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.
172
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
