EXISTS and BETWEEN dates

[simon10]

I would be so grateful for some help as I have been stuck on this problem for a while.

I have two tables:

"properties" with columns - property_id, property_name
"availability" with columns - avl_id, property_id, date

eg..

property: 1,house 1 and avl: 1,1,2008-12-20

The availability table holds a row for each day a property is booked. Therefore it can have numerous rows for the same property id.

I am trying to list all properties from the property table which are available ie.. do not have entries/rows between date A and date B.

I have tried numerous SQL queries including:

select p.property_id from property p WHERE EXISTS (SELECT * from availability a where a.date BETWEEN '2008-03-19' AND '2008-03-20') ";

However, I cannot get this to work!
Many thanks in advance!

 

[gmmastros]

I think....

select p.property_id from property p WHERE EXISTS (SELECT * from availability a where [!]a.property_id = p.property_id AND [/!]a.date BETWEEN '2008-03-19' AND '2008-03-20')

Meaning... I think you were comparing all the properties in the availability table for each property in the properties table.

I suspect this query will return the correct information and probably perform better.

Select P.Property_Id, P.Property_Name
From   Property P 
       Left Join Availability A
         On P.Property_Id = A.Property_ID
         And A.Date Between '20080319' and '20080320'
Group By P.Property_Id, P.Property_Name
Having Count(A.avl_id) = 0

If the second one works properly, and you'd like me to explain it, just ask.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simon10]

Many thanks for your reply.

I tried the first one and this only returns the properties that have entries in the availability table. I need it to return all properties in the property table except those whichhave entries corresponding with the date range 9start and end date)

I could not get the second one working

 

[gmmastros]

What error message did you get with the second one?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simon10]

actually - just noticed the typo I added!
It works!!! many thanks for your help

A quick explanation of it would be much appreciated

thanks again

 

[gmmastros]

If I explain anything that you already know, don't be offended. I don't know how much of this you already know, so I could end up explaining things that are obvious.

First, the most important part here is the left join. With a left join, you will get all the records from the table on the left. If the table on the right has matching records, then you will get that, otherwise, you will get NULL.

What this really means is that... if there is a property without a corresponding record in the availability table, that record will still be returned in a (properly written) left join query.

Non-matching rows will return a NULL value, right? Well... notice this...

[tt][blue]Having Count(A.avl_id) = 0[/blue][/tt]

count returns a number of rows. But... if the value is NULL, it is NOT included in the count. So, for each value of A.avl_id that is NOT NULL, a value of 1 is added. If it is null, nothing is added. So, Count(avl_id) = 0 will filter out all records that actually have values in the availability table (sort of).

The 'sort of' is important here too. More specifically, the placement of the date filter code will affect your results. Notice the join condition

[tt][blue] On P.Property_Id = A.Property_ID
And A.Date Between '20080319' and '20080320'[/blue][/tt]

I put the date condition on the join clause. This is extremely important. If I had put the date filter in the where clause, this query would NOT have returned the correct data because non-matching rows would return NULL for the data column, and the WHERE clause condition would filter out the records because NULL cannot be compared with the between operator.

As you can see... there are a couple of subtle, yet important aspects regarding this query. It's important that you understand it all. If there is anything about the previous explanation that you do not understand, let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simon10]

that makes sense. Thanks you so much for your help and time with this matter