Results 1 to 7 of 7

Thread: [RESOLVED] Inner join problem

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Resolved [RESOLVED] Inner join problem

    I assume this belongs in this forum ...

    I've got an Access 2003 type dB with 3 tables, Reservations, Buildings and Units. The Units table has UnitID, BuildingID and UnitNumber fields. The Building Table has BuildingID and BuildingName fields. The Reservation table has a UnitID field as well as a bunch of others but they don't relate to the inner join.

    Here's perhaps a better visual:
    Code:
    Reservation Table                Unit Table            Building Table
    
    ReservationID
    UnitID ......................... UnitID
                                     BuildingID .......... BuildingID
                                     UnitNumber            BuildingName
    I need to get the UnitNumber & BuildingName for each reservation But I can't seem to find the correct syntax to do this.

    I've looked everywhere to find the correct way to join 3 tables in a daisy chain but I cannot find it. Seems to me that I should be able to get both the UnitNumber and the BuildingName simply from having the UnitID in the Reservation Table record.

    TIA
    Ken

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Inner join problem

    There's no way to join three tables directly. All you can do is join two tables. You can, though, join one of those two table to another table. You can have as many two-table joins as you like in the same query and all those joins are done exactly the same way. What that means is that, if you know how to join two tables, you know how to join any number of tables. Here's how you join two tables:
    sql Code:
    1. SELECT t1.SomeColumn, t2.SomeOtherColumn
    2. FROM Table1 t1
    3. INNER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
    Here's how you join three tables:
    sql Code:
    1. SELECT t1.SomeColumn, t2.SomeOtherColumn, t3.YetAnotherColumn
    2. FROM Table1 t1
    3. INNER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
    4. INNER JOIN Table3 t3 ON t2.PrimaryKey = t3.ForeignKey
    As you can see, the second join is exactly the same as the first. You can add as amny more joins as you like too, each exactly the same as the others. It generally doesn't matter what order the tables are joined in either, assuming that they are all inner joins.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: Inner join problem

    Thank you for the reply jmcilhinney. Your answer is what I had been trying but without success. (I built the SQL statement line by line because it made it easier for me to include or exclude a line as desired).

    If I use the following 3 lines I get an empty return:
    Code:
    SQL = "SELECT Reservations.ReservationID, Units.UnitNumber, Buildings.BuildingName FROM RESERVATIONS "
    SQL = SQL & "INNER JOIN Units ON Reservations.UnitID = Units.UnitID "
    SQL = SQL & "INNER JOIN Buildings ON Units.BuildingID = Buildings.BuildingID"
    I believe this follows the syntax properly .. however, it returns empty. So I simplified things ...

    Code:
    SQL = "SELECT Reservations.ReservationID, Units.UnitNumber FROM RESERVATIONS "
    SQL = SQL & "INNER JOIN Units ON Reservations.UnitID = Units.UnitID"
    This returns the UnitNumber and the reservationID as expected.

    If I add a field BuildingID to the reservation table,
    Code:
    SQL = "SELECT Reservations.ReservationID, Buildings.BuildingName FROM RESERVATIONS "
    SQL = SQL & "INNER JOIN Buildings ON Reservations.BuildingID = Buildings.BuildingID
    returns the correct data - however trying both INNER JOINS at the same time ...
    Code:
    SQL = "SELECT Reservations.ReservationID, Units.UnitNumber, Buildings.BuildingName FROM RESERVATIONS "
    SQL = SQL & "INNER JOIN Units ON Reservations.UnitID = Units.UnitID "
    SQL = SQL & "INNER JOIN Buildings ON Reservations.BuildingID = Buildings.BuildingID"
    or 
    SQL = "SELECT Reservations.ReservationID, Units.UnitNumber, Buildings.BuildingName FROM RESERVATIONS "
    SQL = SQL & "INNER JOIN Units ON Reservations.UnitID = Units.UnitID "
    SQL = SQL & "INNER JOIN Buildings ON Units.BuildingID = Buildings.BuildingID"
    Brings me back to where I started with an empty set.

    Seems to me it ought to work but it doesn't ... I'm missing something ...

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

    Re: Inner join problem

    Sounds like what you're missing is data...

    If you have matching data in A & B... but not in C... then you'll get back nothing... when you inner join, you get all or nothing.

    Just for fun, using SSMS, run the same query... with a couple of changes: 1) change it to select * ... 2) change the inner joins to left joins... then run it... if you get data in all fields going across, then maybe there's something wring... but what I suspect will happen is that you'll see data in the fields for Reservations, data for the fields in Units, and then suddenly the Buildings fields will be NULL. Possibly even Units.BuildingID is null... which would be your problem...

    -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: Inner join problem

    After fiddling around for a couple hours I stumbled on the correct syntax and order. I removed the BuildingID field from the Reservation Table as it was just superfluous.:

    Code:
    "SELECT Reservations.ReservationID, Reservations.ArrivalDate, Reservations.DepartureDate, " &
                "Reservations.PaymentStatus, Reservations.ReservationNumber, Units.UnitNumber, " &
                "Buildings.BuildingName FROM " &
                "(Buildings INNER JOIN Units ON Buildings.BuildingID = Units.BuildingID) " &
                "INNER JOIN Reservations ON Units.UnitID = Reservations.UnitID"
    The JOINS are built backwards to the way I thought they should be .. but it works so I'll adjust my thinking.

    Thank you all for your help in this.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] Inner join problem

    I'd say that tg is probably on the right track but I just wanted to point out two things that are unrelated to your issue specifically.

    1. Why does your code in post #3 suggest that your Reservation table has a BuildingID column? If Reservation has a UnitID and Unit has BuildingID then Reservation.BuildingID is at best duplicating data and at worst providing contradictory data.

    2. You appear to be using VB of some sort but it's not clear which. If it's VB.NET then I would suggest using an XML literal to write SQL code. It's much more readable and therefore less error prone. E.g.
    Code:
    Dim sql = <sql>
                  SELECT *
                  FROM MyTable
                  WHERE SomeColumn = @SomeColumn
              </sql>
    Dim command As New SqlCommand(sql.Value, connection)

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: [RESOLVED] Inner join problem

    Thanks jm. The buildingID in the reservation table was experimental as I couldn't seem to get that information from the Unit table. Once I managed to get the syntax in order I removed it. I understand the value of minimizing data both for clarity and speed. But when things don't want to work right I will adjust the fields and try to figure out how to make it do what I want. Then I'll trim the fields down again. I believe they call it normalization.

    You're correct ... I"m using VB 2012. I generally make SQL a public variable, set that and then call public subs to run the SQL depending on what needs to be done. I'll look into XML literals ... thanks.

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