CODEWITHSUNDEEP
mysqlsqlsql-serverdatabase
Fahad Hussain
Fahad Hussain

Reputation: 1185

Why This Query is returning only 1 Row?

Kindly see the Table Structure , Query and the Result.

MeetingRoom

   ID | Area | RoomNo    Capacity   distances 
   --- ----- --------- ---------- ----------
   1   1     R1         10        10        
   2   1     R3         24        4         
   3   8     R4         24        4         
   4   1     R5         10        10

ReservationTable

ReservationID RoomID      DateTimeStart           DateTimeEnd             
------------- ----------- ----------------------- ----------------------- 
1             1           2013-10-10 17:00:00.000 2013-10-10 19:00:00.000

Query 

 Declare @Start Datetime
 Declare @End Datetime

 set @Start='2013-10-10 13:00:00.000'
 set @End='2013-10-10 14:00:00.000'

 select 
     MeetingRoom.ID 
 from 
    MeetingRoom 
    left Join ResReservationTable Res on Res.RoomID = MeetingRoom.ID
 Where 
   Res.DateTimeStart != @Start and Res.DateTimeEnd != @End
   and (Res.DateTimeStart Not Between @Start and @End) 
   and (Res.DateTimeEnd Not Between @Start and @End)

This Query is Returning only one record as it should return 4 Records

Upvotes: 2

Views: 120

Answers (4)

Bohemian
Bohemian

Reputation: 425198

The best approach is to move the stuff from the WHERE clause up into the ON clause:

select 
    MeetingRoom.ID 
from MeetingRoom 
left Join ResReservationTable Res on Res.RoomID = MeetingRoom.ID
and Res.DateTimeStart != @Start and Res.DateTimeEnd != @End
and Res.DateTimeStart Not Between @Start and @End
and Res.DateTimeEnd Not Between @Start and @End

And hey presto, you keep the outer join but still filter the results, plus you now have the best performing version of the query.

It is a very common, and incorrect, assumption that join conditions may only have "key related" expressions, but in fact you can have any condition, even ones on columns not involved in the tables being joined.

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

If you use LEFT OUTER JOIN and in WHERE clause set condition to right-hand table rather than IS NULL in result you have INNER JOIN.

I think this is what you want.

Declare @Start Datetime
Declare @End Datetime

set @Start='2013-10-10 13:00:00.000'
set @End='2013-10-10 14:00:00.000'

select 
     MeetingRoom.ID 
from 
    MeetingRoom 
    left Join ResReservationTable Res on Res.RoomID = MeetingRoom.ID
Where
  (
   Res.DateTimeStart != @Start and Res.DateTimeEnd != @End
   and (Res.DateTimeStart Not Between @Start and @End) 
   and (Res.DateTimeEnd Not Between @Start and @End)
  )
  OR Res.ReservationID IS NULL

I think you must rephrase your WHERE condition. If you want room which fully free (i.e. intervals not intersect) you can use this:

WHERE
   Res.DateTimeStart > @End
   OR Res.DateTimeEnd < @Start
   OR Res.ReservationID IS NULL

Upvotes: 4

somnath
somnath

Reputation: 1335

Can you try this? It uses "Outer" and checks for NULL values which are returned for rooms that have no reservation and returns those rows too.

Declare @Start Datetime
 Declare @End Datetime

 set @Start='2013-10-10 13:00:00.000'
 set @End='2013-10-10 14:00:00.000'

 select 
     MeetingRoom.ID 
 from 
    MeetingRoom 
    left Outer Join ResReservationTable Res on Res.RoomID = MeetingRoom.ID
 Where 
   (Res.DateTimeStart is NULL OR Res.DateTimeEnd is NULL)
   and Res.DateTimeStart != @Start and Res.DateTimeEnd != @End
   and (Res.DateTimeStart Not Between @Start and @End) 
   and (Res.DateTimeEnd Not Between @Start and @End)

Upvotes: 2

Emanuele Paolini
Emanuele Paolini

Reputation: 10172

You should use an inner join... edit You must use outer join as you are doing but consider how null values compare with non null values...

One way is to explicitly accept null date values with an OR clause

Upvotes: 2

Related Questions