mysql left join question

Question

I've got two tables, one holds reservations for a room, and the other is a "mid" table to hold the dates that the room is reserved on (since a reservation could have multiple non-sequential dates).

It looks something like:

Res_table: id, room_id, owner_id

Res_table_mid: id, res_id, date

The res_id column in the res_table_mid references the id of the res_table. I need to get the start and end date of the reservation.

So the query looks something like this:

SELECT * FROM res_table a
LEFT JOIN (SELECT min(date) as start_date, res_id FROM res_table_mid) AS min ON a.id = min.res_id
LEFT JOIN (SELECT max(date) as end_date, res_id FROM res_table_mid) AS max ON a.id = max.res_id

This works as expected, unless the tables are empty or there are no results, in which case it errors with

#1048 - Column 'res_id' cannot be null

Is there a way to write this so that I get the data I need but if there's no results there's also no error?

Thanks!

Answer 1

SELECT   a.id,
         a.room_id,
         a.owner_id,
         MAX(m.date) AS end_date ,
         MIN(m.date) AS start_date
FROM     res_table a
         LEFT JOIN res_table_mid m
         ON a.id = m.res_id
GROUP BY a.id,
         a.room_id,
         a.owner_id;

Answer 2

Select id, room_id, owner_id
From Res_table
    Left Join   (
                Select R2.res_id, Min(R2.Date), Max(R2.Date)
                From Res_table_mid As R2
                Group By R2.res_id
                ) As MinMax
            On MinMax.res_Id = Res_table.Id

In your original query, neither derived table indicates the Group By column. Instead, you are relying on MySQL to guess that it should group by res_id. If I had to wager a guess, I'd say that this might be the source of the problem.

Answer 3

SELECT min(date) AS start_date FROM (
SELECT * FROM res_table a
LEFT JOIN res_table_mid AS b
ON a.id = b.res_id
WHERE a.id = @reservation)

SELECT max(date) AS end_date FROM (
SELECT * FROM res_table a
LEFT JOIN res_table_mid AS b
ON a.id = b.res_id
WHERE a.id = @reservation)