Post reply

Complex multi-table joins?

  • July 20, 2011 at 4:57 am

    #253954

    Something I feel I should really know by now but still confuses me is how to join tables when you have joins within joins (if thats the best way to describe them?!) - example as below:

    select *

    from dbo.table1 as one

    INNER JOIN dbo.table2 as two ON one.[ID] = two.[ID]

    ...

    All well and good .. but if I then want to join table1 with [table2 that is inner-joined with table3] then I am lost. Do I just open a pair of brackets after the INNER JOIN above and effectively repeat the from bit as above??

    Thanks

  • July 20, 2011 at 7:28 am

    #1355586

    maybe an example might help?

    in my example below, i'm joining a bunch of two tables and a view agaisnt another table to get the desired data.

    my primary key columns, instead of just [ID] for the name, are usually the tablenameID or tablenameTBLKEY, to make it a little easier for a developer to follow the links.

    was this what you were looking for? an example of joining another table that technically is not directly related to teh original base table?

    SELECT ColumnList

    FROM TBCENSUS

    INNER JOIN TBCOUNTY

    ON TBCENSUS.COUNTYTBLKEY = TBCOUNTY.COUNTYTBLKEY

    INNER JOIN TBSTATE

    ON TBCOUNTY.STATETBLKEY = TBSTATE.STATETBLKEY

    LEFT OUTER JOIN VW_HUDBLOCKGROUPS VW

    ON TBSTATE.STATECODE = VW.STATECODE

    AND TBCOUNTY.CODE = VW.COUNTYFIPS

    AND REVERSE(SUBSTRING(REVERSE('0000' + TBCENSUS.CENSUSTRACT),1,7)) = VW.CENSUSTRACT

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 20, 2011 at 8:23 am

    #1355664

    Hi Lowell,

    Thanks for the reply - and yes your example is what I was trying to understand. I think my confusion, if I have this right, is that I thought any joins listed below the base table then had to be joined to that base table but from your example this is not the case and so is not neccessary

    Thanks! 🙂

  • July 20, 2011 at 8:43 am

    #1355696

    glad it helped a bit;

    i think of joins a stree branches...they can join on the trunk (base table) or join on a branch-of-a-branche that is attached to the trunk...however many branches/twigs out you need to go, as long as you have something to link them together.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 4 (of 4 total)

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