Results 1 to 7 of 7

Thread: [RESOLVED] Sql statement question ....

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Resolved [RESOLVED] Sql statement question ....

    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:

    Name:  tables.jpg
Views: 241
Size:  65.4 KB

    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

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Sql statement question ....

    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.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: Sql statement question ....

    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.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Sql statement question ....

    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.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: Sql statement question ....

    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.

    Ken

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Sql statement question ....

    There is a nice write up on MySQL joins but I would think they appy to most SQL DBMSs

    http://www.codeproject.com/Articles/...n-of-SQL-Joins

    Here is a visual from there:
    Attached Images Attached Images  
    Please remember next time...elections matter!

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: Sql statement question ....

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width