January 22, 2009 at 8:47 am
Hi Everyone,
Would someone please be kind enough to explain to me how does the syntax work for an outer join (I found using =* a lot easier before this for some reason) :
FROMtbl_DiscCase DC WITH (NOLOCK)
LEFT OUTER JOIN TBL_Susp S WITH (NOLOCK)
ON S.SuspID = DC.SuspID
AND S.StateID = DC.StateID
LEFT OUTER JOIN TBL_Hearing H WITH (NOLOCK)
ON H.HearingID = DC.HearingID
LEFT OUTER JOIN TBL_Accounts A WITH (NOLOCK)
ON A.OwnerID = DC.CaseID
AND A.OwnerTypeID = 7
LEFT OUTER JOIN TBL_CreditTransactions CT WITH (NOLOCK)
ON CT.AccountID = A.AccountID
AND CT.StateID = DC.StateID
AND CT.DebitTransactionID = DT.TransactionID
AND CT.Archived = 0
LEFT OUTER JOIN TBL_Payments P WITH (NOLOCK)
ON CT.PaymentID = P.PaymentID
AND P.Archived = 0
What I don't understand is :
1) Are the above statements basically a series of LEFT OUTER JOINs between tbl_DiscCase and whatever table is named after the LEFT OUTER JOIN statement ?
or is it a LEFT OUTER JOIN between a table and the other table mentioned in the previous LEFT OUTER JOIN statement ?.
2) For this particular statement :
LEFT OUTER JOIN TBL_CreditTransactions CT WITH (NOLOCK)
ON CT.AccountID = A.AccountID
AND CT.StateID = DC.StateID
AND CT.DebitTransactionID = DT.TransactionID
AND CT.Archived = 0
are we saying that the keys are ALL being joined on an outer left join ? or is it only the AccountID's that are being matched as an outer left join, and all subsequent key matches (State, DebitTrans) are equi-joins ?
3) The last statement :
LEFT OUTER JOIN TBL_Payments P WITH (NOLOCK)
ON CT.PaymentID = P.PaymentID
AND P.Archived = 0
...Is this a LEFT OUTER JOIN between the TBL_Payments table and the TBL_CreditTransactions table ? or a LEFT OUTER JOIN between the TBL_Payments table and the tbl_DiscCase table ?
I tried looking at websites/books and though they do explain the syntax for a join, I haven't found any thorough explanation on what a multiple outer joins in a single statements like this means. Any helps is much appreciated.
Thanks in advance !,
Veronica
January 22, 2009 at 8:50 am
The join is between whatever tables are in the join math.
For example, the second join is between TBL_Hearing and tbl_DiscCase, because those are the two that are mentioned in the join math, "ON H.HearingID = DC.HearingID".
The fourth join is between the table mentioned and three other tables, including the first one.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 9:04 am
GSquared (1/22/2009)
The join is between whatever tables are in the join math.For example, the second join is between TBL_Hearing and tbl_DiscCase, because those are the two that are mentioned in the join math, "ON H.HearingID = DC.HearingID".
The fourth join is between the table mentioned and three other tables, including the first one.
Does that help?
Thanks for your response, so for the fourth join TBL_Payments is basically joined to tbl_DiscCase because TBL_Payments is joined to TBL_CreditTransactions which in turn is joined to tbl_DiscCase. However, what throws me off is the statement LEFT OUTER JOIN in the beginning of the fourth join :
LEFT OUTER JOIN TBL_Payments P WITH (NOLOCK)
ON CT.PaymentID = P.PaymentID
AND P.Archived = 0
this left outer join is between which two tables ? TBL_CreditTransactions and TBL_Payments ? or is talking about tbl_DiscCase and TBL_Payments ?
January 22, 2009 at 2:45 pm
Since it has "CT" on the first part of the join math, it's refering to the table that was aliased as "CT", which, in this case, is the credit transactions table. The second part of the join, with "= 0" is a reference to a column in the payments table. (In outer joins, you need to have that kind of thing in the join math, not the Where clause, or you turn them into inner joins.)
To know which table something is being joined to, look at the statement of the join math. It will either use the table name (if it hasn't been aliased) or the table alias (if it has been). Same applies for views and other things that can be joined to, either the name or the alias.
Just in case you haven't run into that concept before, the "alias" is the bit after the table name, which effectively renames the table for the rest of the query. Like "LEFT OUTER JOIN TBL_Susp S", in which the "S" at the end is the alias for that table. So, in that query, any place you see a column prefixed with "S.", it's a column in TBL_Susp.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 8:13 pm
Many thanks GSquared for clearing that up 🙂
Regards,
Veronica
January 23, 2009 at 7:28 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 12:06 pm
Hi everyone,
I am looking for the answer to a question that was asked in this posting, but I didn't see it in the replies. Does anyone know how this join will work? More importantly, can it be rewritten so that we do not join multiple tables to CT?
2) For this particular statement :
LEFT OUTER JOIN TBL_CreditTransactions CT WITH (NOLOCK)
ON CT.AccountID = A.AccountID
AND CT.StateID = DC.StateID
AND CT.DebitTransactionID = DT.TransactionID
AND CT.Archived = 0
are we saying that the keys are ALL being joined on an outer left join ? or is it only the AccountID's that are being matched as an outer left join, and all subsequent key matches (State, DebitTrans) are equi-joins ?
Thank you for your help,
Michael R.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply