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.

  • Your query will return all chambre.id. What exactly do you want? It's better if you provide sample rows and what happens (vs. what you want). Apr 5 '15 at 16:52
  • Thanks you for taking time to answer this, what I want is all chambre.id but all I get is id which are already in reserver. I'll edit my first comment with sample rows Apr 5 '15 at 16:57
  • 1
    This returns 1 and 2 SQL Fiddle Apr 5 '15 at 17:09
  • 1
    Yeah, I tested as well: SQL-fiddle-2 (same as @Martin's) Apr 5 '15 at 17:12
  • 1
    You select may be the same but the query isn't. My bet is that you have a WHERE clause you did not show. Apr 5 '15 at 17:23

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
    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.

  • You just saved me lot of times, thanks you so much. Apr 5 '15 at 17:32
  • Hello my friend, I'm getting a problem with your SQL, this fiddle explain it : sqlfiddle.com/#!9/3edbc/5. As you can see, my request return 1 and 2 but it should only return 2 because the chambre 1 is between dateDebut and dateFin. (I don't know if I should open a new thread ?). Apr 9 '15 at 10:08
  • The query return both 1 and 2 because they are in chambre and because of the RIGHT join (no mater if they are in the other tables). That's what you wanted. It also returns NULL for both, because no row matches your condition (ron.dateFin < '2015-04-04 00:00:00' OR ron.dateDebut > '2015-05-04 00:00:00') I don't see any "between" in the code. Apr 9 '15 at 11:54
  • Ok so what I want is that when there's already chambre.id in reserver, then "right join" won't add this chambre.id. I Have no idea how to do that, any hint ? Apr 9 '15 at 13:08
  • Forget about right and left joins and edit your question adding the wanted output. Apr 9 '15 at 13:12

Recommend you use parentheses to make sure:




depending on which is more correct.

  • Parentheses are not needed unless there are CROSS joins, only to help developers understand the code. The logical order of joining is determined by the placement of the ON clauses. Apr 5 '15 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.