I've never had to try using EXCEPT in sql but I think that is what I need for this. I've stripped off all the irrelevant fields to make this as straightforward as I can here. I'm using VB 2012 and an Access 2003 mdb database which I think accepts EXCEPT although the information I've found on the net seems sketchy at best. Basically, I have 2 tables as follows:
On the form I have a combo box to select the building with BuildingID as the value member. I also have 2 date pickers - 1 for the arrival date(dtArrival) and 1 for the departure date (dtDeparture).
I have been trying the following sql statement but without success - it always returns empty.
Code:
SQL = "SELECT * FROM Units " &
"INNER JOIN Reservations ON Units.UnitID = Reservations.UnitID " &
"WHERE Units.BuildingID=" & cboBuilding.SelectedValue &
" EXCEPT " &
"SELECT * FROM Reservations " &
"INNER JOIN Units ON Reservations.UnitID=Units.UnitID " &
"WHERE ArrivalDate>=#" & dtArrival.Value &
"# AND DepartureDate<=#" & dtDeparture.Value & "#"
What I'm looking for are the available UnitNumbers in a particular building between the selected arrival and departure dates.
For example, if cboBuilding.Selectedvalue = 2, dtArrival.Value=5/1/2014 and dtDeparture.Value = 7/1/2014 I want the sql statement to return Unit A and Unit C only. I can't seem to figure out how this is done in sql.
Am I on the right track with EXCEPT or missing something entirely? TIA,
Ken
It's been my experience with the EXCEPT (and admittedly it's very limited) that the results of the EXCEPT needs to match field-for-field the results of the main query - that's how it works, by looking for records in the except that are also in the main query and discarding them. At least every example I've ever tried to look up has it that way, but usually the example only selects a coupld fields and I usually need a couple dozen.
But I'm not sure that's warranted in this case.
This is based on the tables above and the limited fino you've given:
Code:
select *
from Units U
left join Reservations R on U.UnitID = R.UnitID -- are unit ids unique even for different buildings?
and ((R.ArrivalDate = #{insert date}#)
and (R.DepartureDate = #{insert date}#))
where R.ID is null -- or use R.UnitID if there is no ID
Basically you're looking for stuff in Units where there is no match in Reservations.
Thanks for the reply TG. The Units.UnitID field is autonumbered so it's unique and never null - the BuildingID will never be null either. What I was wanting to do is fill a grid with available units for the selected time frame and the selected building. However, the Reservation table will not necessarily contain a reservation for any units in a particular building so the sql will return empty. I'm not sure how to handle that - basically return all the units in that building instead of null.
I could, of course, fill an array with all the possible units with 1 sql and then set another array with all the occupied units in that time period with another sql. Then remove duplicates or subtract entries from the second array which would leave me with what I want. Seems somewhat tedious that way round but perhaps it's the only way to get the correct result. I was thinking the EXCEPT statement might do the same thing.
You didn't pay attention to what I did in the query... a LEFT join ... which will return ALL records on the left (Units) and will include fields from where there are matching rows on the right (Reservation) ... as opposed to an INNER join which will only return where there are records in BOTH tables.
So if you
select *
from A
left join B on A.ID = B.AID
then you will get all records in A, and for rows where there is a matching row in B, you'll get the data from that. If there are no matching rows, you still get everything from A, but the fields from B will be null.
Sooooo.... if you want to know all the rows in A where there ISN'T a matching row in B....
select *
from A
left join B on A.ID = B.AID
where B.ID is null
Now that will give you all the rows in A that does NOT have a matching row in B... which, based on your original request IS what you wanted.
Thanks for putting up with me, tg ... I've been writing code since 1970 but until now I've been able to get by with pretty simple sql. This is working sort of backwards to my mind ...
I follow the joins ... but where I'm feeling dense is in understanding the "B.ID is null". There will always be a unitid for every record in the reservation table. (The image in the first post shows the data in each table).
I've spent another few hours trying different things but I can't seem to grasp the null part.
for instance:
Code:
SELECT UnitNumber as [Unit] FROM Units " &
"LEFT JOIN Reservations ON Units.UnitID=Reservations.UnitID " &
"WHERE #" & dtArrival.Value & "#>=Reservations.ArrivalDate
If I set the dtarrival to the day of the Reservation.ArrivalDate I get a return of the occupied unit (Unit B). If I set dtArrival to a day before Reservation.ArrivalDate I get an empty return. What I'm wanting is the opposite so I get Unit A and Unit B and Unit C when I now get nothing and Unit A and Unit C when I now get Unit B. I'm sure this has to do with your B.ID is null but I'm not understanding how to implement this in my code.
Also the minute I put a conditional of "AND BuildingID= something", all returns are empty. If I leave out the date conditionals I get a full list of all the units in each building.
Thanks for the visual ... that helped me get things straight in my head. After a lot of fiddling around with conditions I now have things working the way I want them. For anyone reading this and wanting to see what I ended up with ...
Code:
SELECT UnitNumber FROM Units
LEFT JOIN Reservations ON Units.UnitID=Reservations.UnitID
WHERE
Units.BuildingID=" & cboBuilding.SelectedValue
AND
((#" & dtDeparture.Value & "#<=Reservations.ArrivalDate OR #dtArrival.Value & "#>=Reservations.DepartureDate)
OR
Reservations.UnitID IS NULL)
This returns empty units in the selected building between the selected dates. I haven't given this energetic testing but the logic makes sense to me so it ought to work.
Thank you both for your help in this. I think I grasp joins much better now.
Ken