SQLTeam.com | Weblogs | Forums

Inner joins



Hi all,

I have the below query but its looping. Is there any other way to write this using joins.

select a.chgdat, a.chgusr, a.clm_copied_flag, a.batdat, a.batseq, a.seqnbr, b.linnbr, c. MSGCOD,c.MODDSC,

a., b.

FROM [GNT2].[dbo].[HMCFIL] a, [GNT2].[dbo].[HMCDTL] b, [GNT2].[dbo].[CLMMSG] c

where a.chgusr = 'CWATKIN2'

and a.chgdat >= '03/02/2016'

and a.clm_copied_flag = 'y'

and a.batdat = b.batdat

and a.batseq = b.batseq

and a. seqnbr = b.seqnbr

and c.batdat = b.batdat

and c.batseq = b.batseq

and c. seqnbr = b.seqnbr

and c.linnbr = b.linnbr


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
        ON  a.batdat = b.batdat
        AND a.batseq = b.batseq
        AND a.seqnbr = b.seqnbr
        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

But that's just my 2-pennyworth


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 ?


My coding-style is to always include OUTER (also optional) to highlight a "difference to normal", but no other particular reason.

Yes ... but of course it is worthless!!


No it's not. We (tax payers) pay about 2 cents for each penny. :slight_smile:


If only my tax bill was in Pennies!


That will make my query ugly :smiley:
If i can't make my query fast, at least make it looks beautiful right ?:grin:


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).