I have a table where I store some header informations. Each row is a reservation and there is a field where I store how many guests are in that reservation (table: reservation_header)
ResId | guests |
---|---|
1 | 3 |
2 | 5 |
Then I have a table where I store details about each guest (table: reservation_row)
Id | ResID | name | surname |
---|---|---|---|
1 | 1 | Joe | Smith |
2 | 1 | Frank | Green |
Now what I want to achieve is: for a reservation_header.ResId
join the reservation_row table as many times as guests value (ie 3 times) and show null if the guest is not in reservation_row.
If I do
SELECT rh.ResId, rh.guests, rr.id,rr.name, rr.surname
FROM reservation_header rh LEFT JOIN reservation_row rr
WHERE rh.ResId=1
I get only two records and I miss the third one with null name and surname.
Expected result is:
Id | ResID | name | surname |
---|---|---|---|
1 | 1 | Joe | Smith |
2 | 1 | Frank | Green |
NULL | 1 | NULL | NULL |
But I cannot get the third row. Obvilusly the number of rows in the expected result will be always equal to reservation_header.guests
value so for ResId 2 I'll be looking for 5 rows (all with null value with these sample data)
How can I get it?
CodePudding user response:
Create a helper table with running numbers to generate extra rows
with recursive counters as (
select 1 AS counts
union
select counts 1
from counters
where counts < ( select max(guests) from reservation_header)
),
guests as (
select *, row_number() over(partition by ResID order by id) as rn
from reservation_row
)
select r.id, rh.ResID, r.name, r.surname
from reservation_header rh
join counters c on rh.guests >= c.counts
left join guests r on rh.ResID = r.ResID and r.rn = c.counts
order by rh.resID
;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b6cf01493438847fbe6c84f1913e1ef8
CodePudding user response:
When you add values to the reservation_row table ,at that time you have to insert rows according to the no of guests. here you should have added 3 rows to reservation_row table, which 3rd row with null values. then you should be able to join two tables.
SELECT rh.ResId, rh.guests, rr.id,rr.name, rr.surname
FROM reservation_header rh RIGHT JOIN reservation_row rr
ON rh.ResId=rr.ResID AND rh.ResId=1