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)

Then I have a table where I store details about each guest (table: reservation_row)

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:

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