Mysql – Inner Join and Right Join same query

innodbjoin;MySQL

I'm using MySQL and InnoDB,
I have a table reserver with 2 columns : idReservation and idChambre
I have a table reservation and a table chambre

Now I want my query to return every "chambre.id" who is not between my dateDebut and dateFin.

Below is my actual query :

SELECT cre.id
FROM   reserver AS rer
       INNER JOIN reservation AS ron
         ON rer.idReservation = ron.id
       RIGHT JOIN chambre AS cre
         ON rer.idChambre = cre.id
WHERE (ron.dateFin < ?
    OR ron.dateDebut > ?
    OR (ron.dateFin = NULL AND ron.dateDebut = NULL))

My problem is that my return value only contains chambre when the id of the chambre is already in reserver.

My second problem is that "RIGHT JOIN" add every chambre to my query, but what I want is only chambre as : "ron.dateFin < ? OR ron.dateDebut > ?" or chambre who are not already on linked to reserver.

EDIT : here's some sample rows :

reserver :

id |idReservation | idChambre
---+--------------+----------
0  | 1            | 1

chambre :

id | idCategorie
---+------------
1  | 1
2  | 2

reservation :

id | date       | dateDebut  | dateFin
---+------------+------------+---------
1  | 03/04/2015 | 01/04/2015 | 06/04/2015
2  | 05/04/2015 | 08/04/2015 | 12/04/2015

I want my query to return id 1 and 2 for chambre but all I get actually is 1

P.S : Sorry, english is not my native language but stackexchange is such an amazing website.

Best Answer

When you use a column from a table on the "wrong"* side of an OUTER join, the join usually becomes equivalent to an INNER join.

* "wrong": right side of a LEFT join and left side of a RIGHT join.

So, the solution is usually to move the condition from the WHERE to the ON of the outer join. Like this: 

SELECT cre.id,
       rer.idChambre                 -- to actually see NULL
                                     -- where there is no match
FROM   reserver AS rer
       INNER JOIN reservation AS ron
         ON rer.idReservation = ron.id
       RIGHT JOIN chambre AS cre
         ON rer.idChambre = cre.id
         AND (ron.dateFin < ?
             OR ron.dateDebut > ?
             OR (ron.dateFin IS NULL AND ron.dateDebut IS NULL)) ;
  • Another issue is the = NULL that have to be converted to IS NULL. Nulls are tricky and are not equal to anything, not even to themselves.

It seems it wass not very clear what exactly you wanted. Your issue - as far as I can understand from the question and the following comments - is that you want:

Find all rooms (chambres) that are not reserved for the specific period (4 April 2015 to 4 May 2015)?

Then your query could be rewritten like this:

SELECT cre.id
FROM   chambre AS cre
       LEFT JOIN 
                 reserver AS rer
                 INNER JOIN reservation AS ron
                 ON rer.idReservation = ron.id
                 AND (ron.dateFin > '2015-04-04 00:00:00'
                    AND ron.dateDebut < '2015-05-04 00:00:00')
       ON rer.idChambre = cre.id
WHERE 
    rer.idReservation IS NULL ;

See the SQLfiddle-2 for various other ways to write it (Please avoid, RIGHT joins as much as you can. They confuse many developers who are used to work with LEFT outer joins only.

Related Question