Tricky: Left join relation with row value

Question

Suppose that I have the following two tables:

PRICE
price_id    price       room_id     nr_of_people
1           80          1           1
2           75          1           2
3           90          2           2
4           120         3           3


ROOM
room_id     room_no     max_people
1           101         2
2           102         3
3           103         4

And, I need the following result:

QUERY_RESULT
price       room_no     nr_of_people
80          101         1
75          101         2
0           102         1
90          102         2
0           102         3
0           103         1
0           103         2
120         103         3
0           103         4

The tricky part in here is, I need to retrieve price for each people (for 2 people, for 3 people, for 4 people; that is incrementing upto the max_people defined in the room table), if there is no actual data available in the price table, it should fill in 0 by default. Above illustration should support my explanation.

I am not sure whether the table structure have any logical error.

Any thought/input/help regarding how to resolve the issue is much appreciated.

Answer 1

You should create a table where you have the nr_of_people from 1 up to max_people for each room, and the room_id. Then you can do an OUTER JOIN do get the information as you asked.

You can also create it as a temporary table constructing a query with the data you need in your code.

mysql> CREATE TEMPORARY TABLE nr ( nr_of_people int, room_id int );
Query OK, 0 rows affected (0.00 sec) 

mysql> INSERT INTO nr VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 2 ), ( 2, 2 ), 
( 3, 2 ), ( 1, 3 ), ( 2, 3 ), ( 3, 3 ), ( 4, 3 );
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT price.price, room.room_no, nr.nr_of_people 
FROM price 
RIGHT OUTER JOIN nr ON price.room_id = nr.room_id AND price.nr_of_people = nr.nr_of_people 
INNER JOIN room ON nr.room_id = room.room_id;
+-------+---------+--------------+
| price | room_no | nr_of_people |
+-------+---------+--------------+
|    80 |     101 |            1 |
|    75 |     101 |            2 |
|  NULL |     102 |            1 |
|    90 |     102 |            2 |
|  NULL |     102 |            3 |
|  NULL |     103 |            1 |
|  NULL |     103 |            2 |
|   120 |     103 |            3 |
|  NULL |     103 |            4 |
+-------+---------+--------------+
9 rows in set (0.00 sec)

Answer 2

As abresas' answer and xQbert's comments suggest, you somehow need to create data in order to join it with your tables.

Like abresas' answer, I use an auxiliary table, but in my solution, this table needs to be filled with numbers 1 to N only, where N = biggest value that can ever appear on column max_people.

I created an auxiliary table called aux with a single column num. This query works for me:

SELECT IF(price.price IS NULL, 0, price.price) AS price, room.room_no, aux.num AS nr_of_people
FROM room
JOIN aux ON aux.num <= room.max_people
LEFT JOIN price ON ( price.room_id = room.room_id
AND aux.num = price.nr_of_people )
ORDER BY room.room_id, num

Unfortunately, mysql doesn't provide a native mechanism to generate a sequence of integers (see these questions), so physically creating the auxiliary table seems to be the most practical way to achieve what you need, though workarounds certainly exist if you really can't or don't want to create such table.

Just for the fun of it, the following would work without creating a new table (all inspired in the questions I linked to):

SELECT [...]
FROM room
JOIN (
    SELECT 1 num
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    -- ...add as many entries as needed...
) aux ON aux.num <= room.max_people
LEFT JOIN [...]

As well as this:

SELECT [...]
FROM room
JOIN (
    SELECT @row := @row +1 AS num
    FROM any_table_that_is_big_enough, (SELECT @row :=0) r
) aux ON aux.num <= room.max_people
LEFT JOIN [...]