SELECT query does not "LOOP". You probably missed out some join condition such that you are performing cartesian join on some of the tables
use ANSI join syntax. It will help you to verify and identify any missing condition
FROM [GNT2].[dbo].[HMCFIL] a
INNER JOIN [GNT2].[dbo].[HMCDTL] b
ON a.batdat = b.batdat
AND a.batseq = b.batseq
AND a.seqnbr = b.seqnbr
INNER JOIN [GNT2].[dbo].[CLMMSG] c
ON b.batdat = c.batdat
AND b.batseq = c.batseq
AND b.seqnbr = c.seqnbr
AND b.linnbr = c.linnbr
WHERE a.chgusr = 'CWATKIN2'
AND a.chgdat >= '03/02/2016'
AND a.clm_copied_flag = 'y'
Its just personal preference, but I prefer to keep the Joined Table's Columns on the LEFT of the Join Syntax. That way, mentally, I check that I have "all necessary columns satisfied" for the target table - usually that is all the PKey columns, sometimes it includes a CONSTANT, sometimes it includes a Comment saying that I don't need a particular column - e.g. the Item No column in Invoice Detail. In the example above all the column names are 6 characters, so they all line up neatly ... and thus as easy to check Left Columns or Right Columns, but where column names are variable length, or expressions / constants are involved checking the ones on the right is more error prone - they might stretch off the right side of the screen and an error get overlooked as a consequence etc.
FROM MyInvoiceHeader AS H
JOIN MyInvoiceItems AS I
ON I.InvoiceNo = H.InvoiceNumber
-- AND I.ItemNo -- N/A - Include all items
AND I.Status = 9 -- 9=Cancelled
I think that the Alias is also important so that if I accidentally do:
FROM MyInvoiceHeader AS H
JOIN MyInvoiceItems AS I
ON I.InvoiceNo = H.InvoiceNumber
JOIN MyDeliveryItems AS D
ON I.InvoiceNo = H.InvoiceNumber
^--- Should be "D"
AND D.ItemNo = I.ItemNo
then I am more likely to spot the mistake easily (because it just "feels" wrong NOT having the D. alias on the Left-Side for all JOIN conditions)
If I leave the Alias off (i.e. the column names are unambiguous) there is a greater risk that I join columns from "another table" by mistake
Yes. I do notice that most has the same preference as yours. For me it is the other way.
FROM Table1 t1 INNER JOIN Table2 t2
ON t1.left_table_column = t2.right_table_column
my thinking is Table on the left joining table on the right. So naturally, the join table columns are in that way also.
I notice that you omit the INNER keyword. I know it is optional, I prefer to have it there so that my table join are all in 2 words : INNER JOIN, LEFT JOIN, CROSS APPLY etc.
That is just my 2 cents. But i guess your 2 -penny is worth more than my 2 cents. By at least 2 times ? Is penny still in circulation ?
Yup. Always bothers me the requests for help on here where the Cut & Paste code sample has no formatting and no thought whatsoever as to layout, consistency nor style.
But higher up the list than "beautiful" I put "Defensive programming" so, for me, that outweighs using INNER - so I use OUTER only on LEFT joins and do not use INNER at all (as my perception is that that helps me catch INNER / OUTER misuse more easily).
