too many joins in this SQL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

too many joins in this SQL?

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-08-18 : 14:53:27

This SQL is returning alot of rows. Too many according to the consultant. I am wondering if the inner joins are bringing in too many rows.

SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, SOP10200.EXTDCOST, SOP10200.XTNDPRCE, SOP10200.ITEMNMBR, IV40600.UserCatLongDescr, CATS.UserCatLongDescr AS Expr1, SOP10100.SOPNUMBE,
SOP10106.USRDEF05, SOP10100.DOCDATE, SOP10200.SOPTYPE
FROM SOP10200 AS SOP10200 INNER JOIN
IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
SOP10106 AS SOP10106 ON SOP10200.SOPTYPE = SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = SOP10106.SOPNUMBE INNER JOIN
SOP10100 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN
IV40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN
IV40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-18 : 14:58:11

Put your FORMATTED code between [C0DE][/C0DE]
so we can read it and maybe we can help

SELECT iv00101.itmgedsc, 
       iv00101.itemdesc, 
       rm00101.custname, 
       sop10200.extdcost, 
       sop10200.xtndprce, 
       sop10200.itemnmbr, 
       iv40600.usercatlongdescr, 
       cats.usercatlongdescr AS expr1, 
       sop10100.sopnumbe, 
       sop10106.usrdef05, 
       sop10100.docdate, 
       sop10200.soptype 
FROM   sop10200 AS sop10200 
       INNER JOIN iv00101 AS iv00101 
         ON sop10200.itemnmbr = iv00101.itemnmbr 
       INNER JOIN sop10100 AS sop10100 
         ON sop10200.soptype = sop10100.soptype 
            AND sop10200.sopnumbe = sop10100.sopnumbe 
       INNER JOIN sop10106 AS sop10106 
         ON sop10200.soptype = sop10106.soptype 
            AND sop10200.sopnumbe = sop10106.sopnumbe 
       INNER JOIN sop10100 AS rm00101 
         ON sop10100.custnmbr = rm00101.custnmbr 
       INNER JOIN iv40600 AS iv40600 
         ON iv00101.itmgedsc = iv40600.uscatval 
       INNER JOIN iv40600 AS cats 
         ON iv00101.uscatvls_2 = cats.uscatval

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-18 : 14:58:54

would need to see the data. is the join(s) creating dupes in the recordset? are you joining on PK fields?

hey Adam, how 'bout formatting your code next time. not trying to be mean, just makes it a lot more readable. you've been here long enough to use the [ code ] [ /code ] tags...