Post reply

4 table join - easy one for you guys

  • October 28, 2009 at 1:50 pm

    #139243

    I have four tables:

    loan

    borrower

    property

    status

    They all have the same column (loanid) I need to join all four of them.

    What's the best way to accomplish this?

  • October 28, 2009 at 2:00 pm

    #1072049

    From the information provided:

    SELECT [...]

    FROM loan L

    INNER JOIN borrower B ON B.LoanID = L.LoanID

    INNER JOIN property P ON P.LoanID = L.LoanID AND P.LoanID = B.LoanID

    INNER JOIN status S ON S.LoanID = L.LoanID AND S.LoanID = B.LoanID AND S.LoanID = P.LoanID

    Of course, this might vary by the row counts of the tables and a lot of other factors you don't include.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 28, 2009 at 2:12 pm

    #1072057

    This seems a little excessive, since all these tables pertain to a given loan id why can't it be :

    SELECT ..

    FROM loan L

    JOIN Borrow B on B.LoanId = L.LoanId

    JOIN property P on P.LoanId = L.LoanId

    JOIN status S on S.LoanId = L.LoanId

    I would try it both ways and eyeball the data to make sure they're equivalent and also run the query plan for each.

  • October 28, 2009 at 2:20 pm

    #1072064

    William Plourde (10/28/2009)

    This seems a little excessive, since all these tables pertain to a given loan id why can't it be :

    SELECT ..

    FROM loan L

    JOIN Borrow B on B.LoanId = L.LoanId

    JOIN property P on P.LoanId = L.LoanId

    JOIN status S on S.LoanId = L.LoanId

    I would try it both ways and eyeball the data to make sure they're equivalent and also run the query plan for each.

    It can be, the two queries will return the results; mine just provides the optimizer with additional information that could affect join order. Because I have no idea what the size/rowcounts of the tables are or any other factors, it seemed prudent to include them. That said, I'm not 100% sure it is still necessary to do this, so I'll wait for one of the bigger guns to confirm whether or not this is a relevant factor before I go into any further detail.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 28, 2009 at 2:21 pm

    #1072065

    Thanks Bro!!

  • October 28, 2009 at 2:39 pm

    #1072078

    It can be, the two queries will return the results; mine just provides the optimizer with additional information that could affect join order. Because I have no idea what the size/rowcounts of the tables are or any other factors, it seemed prudent to include them. That said, I'm not 100% sure it is still necessary to do this, so I'll wait for one of the bigger guns to confirm whether or not this is a relevant factor before I go into any further detail.

    Would like to read more about this and whether or not it is a relevant issue...

  • October 28, 2009 at 8:09 pm

    #1072186

    "It Depends". Which table has ALL Loan_ID's and what do you want to actually return? There may be the need for an "OUTER" join in there but, like I said, "It Depends".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply