April 3, 2017 at 12:49 pm
Luis Cazares - Monday, April 3, 2017 11:59 AMChrisM@home - Monday, April 3, 2017 11:46 AMDon't change the order or sequence of those ON clauses.
It's a parent LEFT JOIN child INNER JOIN grandchild relationship.There are no outer joins in the posted query.
Thanks Luis, I missed that.
Quite a few query designers spit out this stuff, usually without table aliases.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 4, 2017 at 6:41 am
This syntax stems from the days when CTE's didn't exist, and subqueries were painful enough, that there was at least some incentive to use it. The idea was mostly useful following a LEFT OUTER JOIN, so that you could INNER JOIN to something that you had LEFT OUTER JOINed to, without forcing a record to appear in the final result set. It was all a matter of the getting the order of the joins correct.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
April 4, 2017 at 8:27 am
Luis Cazares - Monday, April 3, 2017 12:21 PMmw112009 - Monday, April 3, 2017 12:03 PMFolks
I am not interested in you wasting time on this. It works it works... But I have no clue what those joins are doing...
I guess I need some education on inner joins ( The next level )I did see one person commenting that the syntax was correct and it represented a child, grandchild relationship. COOL!
Can that person just give us a example using some table in the Adventureworks DBIf you don't want people to waste their time, you should learn to format your code to make it readable. As mentioned, Chris posted an example and we provided the link to it (twice). On OUTER JOINs the order will matter, but in this case it won't. You'll hardly see the need for this syntax and most (or all) of the time you can write it on a more comprehensive way.
Here's your query with proper format.
SELECT C2.FormNbr,
C2.AdmitDate,
C2.Membernbr,
C2.TotalNetAmt,
C1.TotalNetAmt AS TotalNetAmt2,
CSL2.NetAmt,
CSL2.ProcedureCode,
CSL2.ProviderNbr,
C2.PlaceofService,
C2.ClaimType,
C1.FormNbr AS FormNbr2,
CSL1.ProcedureCode AS ProcedureCode2,
CSL1.ProviderNbr AS ProviderNbr2,
C2.PlanCode
FROM CLAIM.DClaim AS C1
JOIN CLAIM.DClaimServiceLine AS CSL1 ON C1.ClaimTID = CSL1.ClaimTID
JOIN CLAIM.DClaim AS C2 ON C1.Membernbr = C2.Membernbr
AND C1.AdmitDate = C2.AdmitDate
AND C1.FormNbr > C2.FormNbr
AND C1.ClaimType = C2.ClaimType
JOIN CLAIM.DClaimServiceLine AS CSL2 ON C2.ClaimTID = CSL2.ClaimTID
AND CSL1.NetAmt = CSL2.NetAmt
WHERE C2.AdmitDate BETWEEN @StartAdmitdate AND @ENDAdmitDate
AND C2.TotalNetAmt > 0
AND C1.TotalNetAmt > 0
AND CSL2.NetAmt > 500
AND CSL2.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514')
AND CSL1.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514');
BTW - I did format using the tool http://www.dpriver.com/pp/sqlformat.htm Do you know of a better tool that can do the formatting ?
April 4, 2017 at 8:29 am
Luis C- Thanks! That was a great help....
April 4, 2017 at 8:47 am
mw112009 - Tuesday, April 4, 2017 8:27 AMBTW - I did format using the tool http://www.dpriver.com/pp/sqlformat.htm Do you know of a better tool that can do the formatting ?
I format the code myself.
I haven't found a free formatting tool that format the code as I like.
You also used code with bad syntax which made the formatting go crazy.
April 5, 2017 at 1:26 am
Don't know if it still does, but I think it used to influence the plan. Something about forcing join order? so you'd have
SELECT *
From TableX
INNER JOIN tableY
INNER JOIN tableZ
ON tableY.ID=tableZ.ID
ONtableX.ID=tableY.ID
I assumed that it was like a sub query - the tableY and tableZ results return and limit the tableX results.
It used to be faster. (well, it was the time I actually checked). Personally, I use it to put some hierarchy into the query. Y and Z are related and X is related to the product of the two.
Well, I think so anyway.
April 5, 2017 at 1:32 am
daveas - Wednesday, April 5, 2017 1:26 AMDon't know if it still does, but I think it used to influence the plan. Something about forcing join order? so you'd have
SELECT *
From TableX
INNER JOIN tableY
INNER JOIN tableZ
ON tableY.ID=tableZ.ID
ONtableX.ID=tableY.IDI assumed that it was like a sub query - the tableY and tableZ results return and limit the tableX results.
It used to be faster. (well, it was the time I actually checked). Personally, I use it to put some hierarchy into the query. Y and Z are related and X is related to the product of the two.
Well, I think so anyway.
Ah. It seems to have filtered out the 4 spaces I carefully put on the 4th and 5th rows.
SELECT *
From TableX
INNER JOIN tableY
....INNER JOIN tableZ
....ON tableY.ID=tableZ.ID
ONtableX.ID=tableY.ID
so, with dots instead of spaces.
April 5, 2017 at 12:18 pm
daveas - Wednesday, April 5, 2017 1:32 AMdaveas - Wednesday, April 5, 2017 1:26 AMDon't know if it still does, but I think it used to influence the plan. Something about forcing join order? so you'd have
SELECT *
From TableX
INNER JOIN tableY
INNER JOIN tableZ
ON tableY.ID=tableZ.ID
ONtableX.ID=tableY.IDI assumed that it was like a sub query - the tableY and tableZ results return and limit the tableX results.
It used to be faster. (well, it was the time I actually checked). Personally, I use it to put some hierarchy into the query. Y and Z are related and X is related to the product of the two.
Well, I think so anyway.
Ah. It seems to have filtered out the 4 spaces I carefully put on the 4th and 5th rows.
SELECT *
From TableX
INNER JOIN tableY
....INNER JOIN tableZ
....ON tableY.ID=tableZ.ID
ONtableX.ID=tableY.IDso, with dots instead of spaces.
That's because you didn't specify that it was preformatted text by using the SQL Code tags listed as the penultimate option on the post dialog box.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 5, 2017 at 12:51 pm
Luis Cazares did provide a solution to this. So this is done! I got what I wanted... Thx to luis
April 5, 2017 at 12:57 pm
mw112009 - Wednesday, April 5, 2017 12:51 PMLuis Cazares did provide a solution to this. So this is done! I got what I wanted... Thx to luis
And did you understand what I did and suggested?
April 5, 2017 at 2:52 pm
Luis Cazares - Wednesday, April 5, 2017 12:57 PMmw112009 - Wednesday, April 5, 2017 12:51 PMLuis Cazares did provide a solution to this. So this is done! I got what I wanted... Thx to luisAnd did you understand what I did and suggested?
yes yes... YOU been a great help
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply