CODEWITHSUNDEEP
mysql
Danny Hobo
Danny Hobo

Reputation: 694

MySQL join to see if record exists in other table

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 ?

Upvotes: 5

Views: 7994

Answers (5)

Nabi K.A.Z.
Nabi K.A.Z.

Reputation: 10734

The answer from "Ross McNab" is the best, but if you don't have use condition in JOIN you can use this sample code:

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

Upvotes: 0

Erhan Akpınar
Erhan Akpınar

Reputation: 116

SELECT ACCOMMODATION.ID, ACCOMMODATION.NAME,
       ACCOMMODATION_EXCLUDE.ID_ACCOMMODATION,
       ACCOMMODATION_EXCLUDE.ID_COURSE
  FROM ACCOMMODATION, ACCOMMODATION_EXCLUDE
 WHERE ACCOMMODATION.ID = ACCOMMODATION_EXCLUDE.ID_ACCOMMODATION(+)
       AND ACCOMMODATION_EXCLUDE.ID_COURSE = 16

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

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.

Upvotes: 1

Ross McNab
Ross McNab

Reputation: 11587

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.

Upvotes: 10

Eoin Murphy
Eoin Murphy

Reputation: 813

It wont give you any more based on your condition

WHERE ae.id_course = 16

It is giving you the proper resultset. Remove that and it will list all of them.

Upvotes: 0

Related Questions