tabaluga tabaluga - 1 month ago 8
MySQL Question

Listing rows not only when criteria are met

I have three tables:
1 dates - contains column date
2 book - contains columns room, date and surname
3 people - contains column surname

I am able to get dates where there were people in rooms on certain days within the range of dates.



SELECT date, IFNULL(surname, 'ghost')
FROM book JOIN dates ON book.date=dates.date JOIN people ON people.surname=book.surname
WHERE room = 113
AND date BETWEEN '2015-01-01' AND '2015-01-07'

+------------+-----------+
| Date | Surname |
+------------+-----------+
| 2015-01-02 | Surname 1 |
| 2015-01-05 | Surname 2 |
+------------+-----------+





How do I return a full list of dates, including those where there was nobody in a room so every date from given range is printed and if no people in the specified room, then 'ghost' is printed instead?

Answer

You need to use a LEFT JOIN

SELECT dates.date, IFNULL(book.surname, 'ghost')
FROM dates
LEFT JOIN book 
    ON  book.date=dates.date
    AND book.room = 113 
-- LEFT JOIN people ON people.surname=book.surname
WHERE dates.date BETWEEN '2015-01-01' AND '2015-01-07'

Note that book.room = 113 needs to be in the ON clause. Using it in the WHERE clause would convert the LEFT JOIN to INNER JOIN.