|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
INNER JOIN With more than one join conditionNeed help in understanding a query which is as follows which has 2 join
conditions Select C1,C2 From T1 INNER JOIN T2 ON T2.C1=T1.C1 INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 SELECT
t1.C1, t1.C2 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 INNER JOIN t3 ON t2.c1 = t3.c1 AND t1.c2 = t3.c2 Show quote "xgopi" <xg***@discussions.microsoft.com> wrote in message news:2B9825F6-BFDA-475A-B136-74A4D4A348CD@microsoft.com... > Need help in understanding a query which is as follows which has 2 join > conditions > > Select C1,C2 > From T1 > INNER JOIN T2 ON T2.C1=T1.C1 > INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 > > Even first i thought so, but it is not "AND" it is "ON" ,I wanted to know
the second "ON" is similar to "AND" or it is something different. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > SELECT > t1.C1, > t1.C2 > FROM t1 > INNER JOIN t2 > ON t1.c1 = t2.c1 > INNER JOIN t3 > ON t2.c1 = t3.c1 > AND t1.c2 = t3.c2 > > > "xgopi" <xg***@discussions.microsoft.com> wrote in message > news:2B9825F6-BFDA-475A-B136-74A4D4A348CD@microsoft.com... > > Need help in understanding a query which is as follows which has 2 join > > conditions > > > > Select C1,C2 > > From T1 > > INNER JOIN T2 ON T2.C1=T1.C1 > > INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 > > > > > > > Parenthesis can be your friend in understanding (and in asserting control.)
In the case of multiple [INNER] joins, the effect is as if [AND] is applied to each additional set of characteristics filtered as a result of the JOIN. SELECT t1.C1 , t1.C2 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 INNER JOIN t3 ON ( t2.c1 = t3.c1 AND t1.c2 = t3.c2 ) -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "xgopi" <xg***@discussions.microsoft.com> wrote in message news:33F93004-E828-45BD-A72D-8E1D0DF02D91@microsoft.com... > Even first i thought so, but it is not "AND" it is "ON" ,I wanted to know > the second "ON" is similar to "AND" or it is something different. > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> SELECT >> t1.C1, >> t1.C2 >> FROM t1 >> INNER JOIN t2 >> ON t1.c1 = t2.c1 >> INNER JOIN t3 >> ON t2.c1 = t3.c1 >> AND t1.c2 = t3.c2 >> >> >> "xgopi" <xg***@discussions.microsoft.com> wrote in message >> news:2B9825F6-BFDA-475A-B136-74A4D4A348CD@microsoft.com... >> > Need help in understanding a query which is as follows which has 2 join >> > conditions >> > >> > Select C1,C2 >> > From T1 >> > INNER JOIN T2 ON T2.C1=T1.C1 >> > INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 >> > >> > >> >> >> Thankx
Show quote "Arnie Rowland" wrote: > Parenthesis can be your friend in understanding (and in asserting control.) > > In the case of multiple [INNER] joins, the effect is as if [AND] is applied to each additional set of characteristics filtered as a result of the JOIN. > > SELECT > t1.C1 > , t1.C2 > FROM t1 > INNER JOIN t2 > ON t1.c1 = t2.c1 > INNER JOIN t3 > ON ( t2.c1 = t3.c1 > AND t1.c2 = t3.c2 > ) > > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "xgopi" <xg***@discussions.microsoft.com> wrote in message news:33F93004-E828-45BD-A72D-8E1D0DF02D91@microsoft.com... > > Even first i thought so, but it is not "AND" it is "ON" ,I wanted to know > > the second "ON" is similar to "AND" or it is something different. > > > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >> SELECT > >> t1.C1, > >> t1.C2 > >> FROM t1 > >> INNER JOIN t2 > >> ON t1.c1 = t2.c1 > >> INNER JOIN t3 > >> ON t2.c1 = t3.c1 > >> AND t1.c2 = t3.c2 > >> > >> > >> "xgopi" <xg***@discussions.microsoft.com> wrote in message > >> news:2B9825F6-BFDA-475A-B136-74A4D4A348CD@microsoft.com... > >> > Need help in understanding a query which is as follows which has 2 join > >> > conditions > >> > > >> > Select C1,C2 > >> > From T1 > >> > INNER JOIN T2 ON T2.C1=T1.C1 > >> > INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 > >> > > >> > > >> > >> > >> The parenthesis are not needed; multiple conditions can be placed within the on clause. Aaron's code is perfect.
-Paul Nielsen SQL Server MVP www.SQLServerBIble.com "Arnie Rowland" <ar***@1568.com> wrote in message news:%23tVcYbYzGHA.3424@TK2MSFTNGP03.phx.gbl... Parenthesis can be your friend in understanding (and in asserting control.) In the case of multiple [INNER] joins, the effect is as if [AND] is applied to each additional set of characteristics filtered as a result of the JOIN. SELECT t1.C1 , t1.C2 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 INNER JOIN t3 ON ( t2.c1 = t3.c1 AND t1.c2 = t3.c2 ) -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "xgopi" <xg***@discussions.microsoft.com> wrote in message news:33F93004-E828-45BD-A72D-8E1D0DF02D91@microsoft.com... > Even first i thought so, but it is not "AND" it is "ON" ,I wanted to know > the second "ON" is similar to "AND" or it is something different. > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> SELECT >> t1.C1, >> t1.C2 >> FROM t1 >> INNER JOIN t2 >> ON t1.c1 = t2.c1 >> INNER JOIN t3 >> ON t2.c1 = t3.c1 >> AND t1.c2 = t3.c2 >> >> >> "xgopi" <xg***@discussions.microsoft.com> wrote in message >> news:2B9825F6-BFDA-475A-B136-74A4D4A348CD@microsoft.com... >> > Need help in understanding a query which is as follows which has 2 join >> > conditions >> > >> > Select C1,C2 >> > From T1 >> > INNER JOIN T2 ON T2.C1=T1.C1 >> > INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 >> > >> > >> >> >> True, the "parenthesis are not needed". There was nothing wrong with Aaron's code, I didn't imply that there was.
However, I find that for those new to SQL, often parenthesis help in conceptualization. Perfection is in the eye of the beholder. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Paul Nielsen (MVP)" <pa***@sqlserverbible.com> wrote in message news:Ol2WzvczGHA.4920@TK2MSFTNGP06.phx.gbl... The parenthesis are not needed; multiple conditions can be placed within the on clause. Aaron's code is perfect.-Paul Nielsen SQL Server MVP www.SQLServerBIble.com "Arnie Rowland" <ar***@1568.com> wrote in message news:%23tVcYbYzGHA.3424@TK2MSFTNGP03.phx.gbl... Parenthesis can be your friend in understanding (and in asserting control.) In the case of multiple [INNER] joins, the effect is as if [AND] is applied to each additional set of characteristics filtered as a result of the JOIN. SELECT t1.C1 , t1.C2 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 INNER JOIN t3 ON ( t2.c1 = t3.c1 AND t1.c2 = t3.c2 ) -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "xgopi" <xg***@discussions.microsoft.com> wrote in message news:33F93004-E828-45BD-A72D-8E1D0DF02D91@microsoft.com... > Even first i thought so, but it is not "AND" it is "ON" ,I wanted to know > the second "ON" is similar to "AND" or it is something different. > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> SELECT >> t1.C1, >> t1.C2 >> FROM t1 >> INNER JOIN t2 >> ON t1.c1 = t2.c1 >> INNER JOIN t3 >> ON t2.c1 = t3.c1 >> AND t1.c2 = t3.c2 >> >> >> "xgopi" <xg***@discussions.microsoft.com> wrote in message >> news:2B9825F6-BFDA-475A-B136-74A4D4A348CD@microsoft.com... >> > Need help in understanding a query which is as follows which has 2 join >> > conditions >> > >> > Select C1,C2 >> > From T1 >> > INNER JOIN T2 ON T2.C1=T1.C1 >> > INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 >> > >> > >> >> >> On Thu, 31 Aug 2006 20:40:02 -0700, xgopi wrote:
>Need help in understanding a query which is as follows which has 2 join Hi xgopi,>conditions > >Select C1,C2 >From T1 >INNER JOIN T2 ON T2.C1=T1.C1 >INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 > Not much to understand - it's illegal syntax. Try running the code in Query Analyzer or SQL Server Management Studio and you'll get this result: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'ON'. -- Hugo Kornelis, SQL Server MVP  |
|||||||||||||||||||||||
Other interesting topics