Results 1 to 13 of 13
  1. #1
    Join Date
    May 2008
    Posts
    1

    Unanswered: Inner Joins to connect Multiple table

    I am not sure if I have used inner joins correctly for my scenario:

    Select distinct col m and N

    where the all following conditions satisfys:

    Table A- inner joined to B on A. a=B.b

    and

    Table B - inner joined to C on B.b1=C.c

    and

    Table C - inner joined to D on C.c1=D.d

    and D. g= 'some value'

    How can I put this condition using Inner Joins:

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    select whatever
    from tablea as a
    inner join tableb as b on (a.a=b.b)
    inner join tablec as c on (b.b1=c.c)
    inner join tabled as d on (c.c1 = d.d)
    ...

    Andy

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    andy, just curious, but why are you coding those completely unnecessary parentheses?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am a software engineer by training. It is for readability -- which lowers maintenance costs.

    Andy

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, i see

    so if you had to join on two columns, would you write

    ON ( t1.x = t2.y ) AND ( t1.foo = t2.bar )

    or would you write

    ON ( t1.x = t2.y AND t1.foo = t2.bar )

    or would you perhaps write

    ON ( ( t1.x = t2.y ) AND ( t1.foo = t2.bar ) )

    or would you do what MS Access does and write

    ON ( ( ( t1.x ) = ( t2.y ) ) AND ( ( t1.foo ) = ( t2.bar ) ) )

    ????


    as you can probably tell, i don't agree that extra parentheses lower maintenance cost

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    You have to be an engineer to understand one
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Both of these are valid way to write the sql, tell me which one you would want to maintain...

    select whatever
    from tablea as a
    inner join tableb as b on (a.a=b.b)
    inner join tablec as c on (b.b1=c.c)
    inner join tabled as d on (c.c1 = d.d)

    removing the unnecessary parenthesis, and oh yes the unnecessary "as"

    select whatever from tablea a inner join tableb b on a.a=b.b inner join tablec c on b.b1=c.c inner join tabled d on c.c1 = d.d

    Even this is more readable then the second, but I would still prefer the first.

    select whatever from tablea as a inner join tableb as b on (a.a=b.b) inner join tablec as c on (b.b1=c.c) inner join tabled as d on (c.c1 = d.d)

    My point is that the prettier and more explicit the code, the easier it is to maintain--at least for me.

    Andy

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ARWinner
    tell me which one you would want to maintain...
    Code:
    SELECT whatever
      FROM tablea AS a
    INNER 
      JOIN tableb AS b 
        ON b.b = a.a
    INNER 
      JOIN tablec AS c 
        ON c.c = b.b1
    INNER 
      JOIN tabled AS d 
        ON d.d = c.c1
    this will look even more maintainable with longer, meaningful column names

    also, note the sequence in which the join columns are mentioned

    by the way, you side-stepped my question: what parentheses do you code when there are two join conditions?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by r937
    by the way, you side-stepped my question: what parentheses do you code when there are two join conditions?
    I usually do it this way: (a.a=b.b and a.c = b.d)
    but I sometimes do it this way: ((a.a=b.b) and (a.c=b.d))


    SQL is really very versatile and the formatting is really up to the preferences of the developer.

    Andy

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ARWinner
    SQL is really very versatile and the formatting is really up to the preferences of the developer.
    i will agree most heartily with this

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    This is a perfcet example of ease of readability and maintenance that Andy was talking about. this is just a small portion f SQL that I was given to rview and I am already ready to go balistic

    select T0.C0 c1
    , T0.C1 c2
    , T0.C2 YYYY_QTR
    , T0.C3 QTR_YY
    , T0.C4 Diverse_Employee_Count
    , sum(T0.C4) over (partition by T0.C3, T0.C0) Standard_Headcount
    , T0.C5 c7
    , T0.PB
    , T0.PBD


    In some cases developer is setting up aliases in others does not.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ARWinner
    I am a software engineer by training. It is for readability -- which lowers maintenance costs.

    Andy

    Great now I have to clean coffee off my monitor
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
        SELECT whatever
          FROM tablea 	AS a
    INNER JOIN tableb 	AS b 
    	ON b.b = a.a
    INNER JOIN tablec 	AS c 
    	ON c.c = b.b1
    INNER JOIN tabled 	AS d 
    	ON d.d = c.c1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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