MySQL join to see if record exists in other table

Question

I have the following tables:

CREATE TABLE `accommodations` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) 

CREATE TABLE `accommodations_exclude` (
  `id_accommodation` int(11) unsigned NOT NULL,
  `id_course` int(11) NOT NULL,
  UNIQUE KEY `id_course` (`id_course`,`id_accommodation`)
) 

In the accommodations table there are 4 records, in the accommodations_exclude there are many more. Now I would like to have a query that always give me all records from the accommodations table, and joined as extra field to see if the accommodation also exists in the accommodations_exclude table.

For example; in the accommodations_exclude there is one row with id_accommodation = 2, id_course = 16.

I want to have a resultset that shows me the following:

accommodation.id, accommodation.name, accommodation_exclude.id_accommodation, accommodation_exclude.id_course

1,'acco 1',null,null
2,'acco 2',2,16
3,'acco 3',null,null
4,'acco 4',null,null

The query I have right now is this one:

SELECT *
FROM accommodations a
LEFT JOIN accommodations_exclude ae ON a.id = ae.id_accommodation 
WHERE ae.id_course = 16

but this gives me only the resultset

2,'acco 2',2,16

and not the accommodations that should have null values

any idea on what i do wrong here ?

Answer 1

Move the ae.id_course = 16 clause from WHERE to your LEFT JOIN

SELECT *
FROM accommodations a
LEFT JOIN accommodations_exclude ae ON
    a.id = ae.id_accommodation
    AND ae.id_course = 16

You should think of WHERE as a filter on the final resultset, anything to do with linking tables should be conditions in your JOINs.

Your original WHERE ae.id_course = 16 was filtering out the NULL ae.id_course rows from the resultset.

Answer 2

Make the accommodations_exclude table the left table, or use RIGHT JOIN instead like so:

SELECT
 a.id, 
 a.name, 
 ae.id_accommodation, 
  ae.id_course
FROM accommodations a
RIGHT JOIN accommodations_exclude ae ON a.id = ae.id_accommodation;

SQL Fiddle Demo

This will give you:

| ID |   NAME | ID_ACCOMMODATION | ID_COURSE |
----------------------------------------------
|  1 | acco 1 |           (null) |    (null) |
|  2 | acco 2 |                2 |        16 |
|  3 | acco 3 |           (null) |    (null) |
|  4 | acco 4 |           (null) |    (null) |

Note that: For the sample data you posted, the WHERE ae.id_course = 16 will return only one row, that had ae.id_course = 16.