tabaluga tabaluga - 1 year ago 61
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 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 Source

You need to use a LEFT JOIN

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

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