SQL query: self join on subquery necessitates creating a separate (non-temporary) table?

Question

I'm working on what is for me a complicated query, and I've managed to get the information I need, but seem to be forced to create a table to accomplish it. I'm using MySQL, so I can't use WITH, I can't use a view because my SELECT contains a subquery in the FROM clause, and I can't use a temporary table because I need to self-join. Am I missing something?

Background:

• a reservation can have 1 or more reservation_detail (foreign key rel'p on reservation_id)
• a reservation_detail has a quantity and a ticket_type (foreign key rel'p on ticket_type)

Here's the first part of my current solution:

CREATE TABLE 
    tmp
SELECT 
    t.reservation_id, 
    t.ticket_type, 
    COALESCE(rd.quantity,0) AS qty
FROM (
    SELECT *
    FROM 
        (ticket_type tt, reservation r)
    ) t
LEFT JOIN 
    reservation_detail rd 
    ON 
        t.reservation_id = rd.reservation_id 
    AND 
        t.ticket_type = rd.ticket_type;

This gives me a table that looks like the following, where for each combination of a reservation_id and a ticket_type, I have a qty.

+----------------+-------------+------+
| reservation_id | ticket_type | qty  |
+----------------+-------------+------+
|              1 | ADULT       |    2 | 
|              1 | CHILD       |    2 | 
|              1 | INFANT      |    0 | 
|              2 | ADULT       |    1 | 
|              2 | CHILD       |    0 | 
|              2 | INFANT      |    0 | 
|              3 | ADULT       |    1 | 
|              3 | CHILD       |    0 | 
|              3 | INFANT      |    0 | 
+----------------+-------------+------+

Now I can self join thrice on this table to get what I'm really looking for...

SELECT 
    t1.reservation_id, 
    t1.qty AS num_adults, 
    t2.qty AS num_children, 
    t3.qty AS num_infants
FROM 
    tmp t1
LEFT JOIN 
    tmp t2 
    ON 
        t1.reservation_id = t2.reservation_id
LEFT JOIN 
    tmp t3 
    ON 
        t2.reservation_id = t3.reservation_id
WHERE 
    t1.ticket_type = 'ADULT'
AND 
    t2.ticket_type = 'CHILD'
AND 
    t3.ticket_type = 'INFANT';

...which is one row for each reservation showing the qty for each of the three ticket types.

+----------------+------------+--------------+-------------+
| reservation_id | num_adults | num_children | num_infants |
+----------------+------------+--------------+-------------+
|              1 |          2 |            2 |           0 | 
|              2 |          1 |            0 |           0 | 
|              3 |          1 |            0 |           0 | 
+----------------+------------+--------------+-------------+

I hope this is enough information. Please leave a comment if it's not.

Answer 1

If you want to stick with your first query, you can sub this for the 2nd:

SELECT reservation_id,
       SUM(CASE WHEN ticket_type='ADULT' THEN qty ELSE 0 END) AS adults,
       SUM(CASE WHEN ticket_type='CHILD' THEN qty ELSE 0 END) AS children,
       SUM(CASE WHEN ticket_type='INFANT' THEN qty ELSE 0 END) AS infants,
FROM tmp
GROUP BY reservation_id;

However, I'm wondering a bit about your schema. You are storing qty, a calculated value. Have you considered just having a row for each ticket instance. If you do that then no tmp table is required, though you'd do the pivot similarly to the above.

Answer 2

Hi nselikoff,

a simple GROUP BY should be OK:

SELECT t.reservation_id,
       SUM(CASE
              WHEN ticket_type = 'ADULT' THEN
               COALESCE(rd.quantity, 0)
              ELSE
               0
           END) num_adults,
       SUM(CASE
              WHEN ticket_type = 'CHILD' THEN
               COALESCE(rd.quantity, 0)
              ELSE
               0
           END) num_children,
       SUM(CASE
              WHEN ticket_type = 'INFANT' THEN
               COALESCE(rd.quantity, 0)
              ELSE
               0
           END) num_infants
  FROM (SELECT * FROM (ticket_type tt, reservation r)) t
  LEFT JOIN reservation_detail rd ON t.reservation_id = rd.reservation_id
                                 AND t.ticket_type = rd.ticket_type
 GROUP BY t.reservation_id

Answer 3

If your query is considering only these 3 types: ADULT, CHILD, INFANT; you don't have to use table ticket_type.

SELECT 
    r.reservation_id, 
    COALESCE(rd_adult.quantity,0) AS num_adults, 
    COALESCE(rd_child.quantity,0) AS num_children, 
    COALESCE(rd_infant.quantity,0) AS num_infants
FROM 
    reservation r
LEFT JOIN 
    reservation_detail rd_adult 
    ON r.reservation_id = rd_adult.reservation_id
       and rd_adult.ticket_type = 'ADULT'
LEFT JOIN 
    reservation_detail rd_child 
    ON r.reservation_id = rd_child.reservation_id
       and rd_child.ticket_type = 'CHILD'
LEFT JOIN 
    reservation_detail rd_infant
    ON r.reservation_id = rd_infant.reservation_id
       and rd_infant.ticket_type = 'INFANT'

Answer 4

Since table reservation_detail contains all the fields you need, you don't need to join the other tables and create a temp table.

Try this:

SELECT distinct
    t.reservation_id, 
    COALESCE(t1.qty,0) AS num_adults, 
    COALESCE(t2.qty,0) AS num_children, 
    COALESCE(t3.qty,0) AS num_infants
FROM reservation t
LEFT JOIN reservation_detail t1 ON t.reservation_id = t1.reservation_id AND t1.ticket_type = 'ADULT'
LEFT JOIN reservation_detail t2 ON t.reservation_id = t2.reservation_id AND t2.ticket_type = 'CHILD'
LEFT JOIN reservation_detail t3 ON t.reservation_id = t3.reservation_id AND t3.ticket_type = 'INFANT';