Duplicate results when doing nested joins

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

Script Library

Duplicate results when doing nested joins

Author

Topic

vlfoley
Starting Member

6 Posts

Posted - 2011-05-24 : 10:20:37

I'm using this query to pull information and I get a duplicate of every row. Not sure if it's something with my joins or what. Could anybody help me?

SELECT dbo.workorder.wonum, dbo.companies.name, dbo.workorder.wopriority, dbo.poline.polinenum, dbo.po.potype, dbo.po.orderdate, dbo.po.status, dbo.poline.ponum, dbo.poline.itemnum, dbo.poline.description, dbo.poline.orderqty, dbo.poline.orderunit, dbo.poline.receivedqty, dbo.poline.unitcost, dbo.poline.linecost, dbo.po.followupdate, dbo.matrectrans.actualdate, dbo.poline.vendeliverydate, dbo.person.lastname
FROM dbo.matrectrans INNER JOIN (dbo.person INNER JOIN (dbo.workorder INNER JOIN ((dbo.po INNER JOIN dbo.poline ON dbo.po.ponum = dbo.poline.ponum) INNER JOIN dbo.companies ON dbo.po.vendor = dbo.companies.company) ON (dbo.poline.refwo = dbo.workorder.wonum) AND (dbo.poline.siteid = dbo.workorder.siteid)) ON dbo.person.personid = dbo.po.purchaseagent) ON (dbo.matrectrans.refwo = dbo.workorder.wonum) AND (dbo.matrectrans.ponum = dbo.po.ponum) AND (dbo.matrectrans.siteid = dbo.workorder.siteid)
WHERE (((dbo.workorder.wonum) Not Like 'GUM') AND ((dbo.po.orderdate)>='4/9/2011') AND ((dbo.po.status) Like 'APPR') AND ((dbo.poline.ponum) Not Like '%J') AND ((dbo.workorder.worktype) Like 'CO') AND ((dbo.workorder.siteid)='WSC')) OR (((dbo.workorder.wonum) Not Like 'GUM') AND ((dbo.po.orderdate)>='4/9/2011') AND ((dbo.po.status) Like 'APPR') AND ((dbo.poline.ponum) Not Like '%J') AND ((dbo.workorder.worktype) Like 'CO') AND ((dbo.workorder.siteid)='WSC'))
ORDER BY dbo.workorder.wonum;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 11:07:44

select count(*)
from (select top 1 * from dbo.matrectrans) matrectrans

select count(*)
from (select top 1 * from dbo.matrectrans) matrectrans
join dbo.workorder
on dbo.matrectrans.siteid = dbo.workorder.siteid

select count(*)
from (select top 1 * from dbo.matrectrans) matrectrans
join dbo.workorder
on dbo.matrectrans.siteid = dbo.workorder.siteid
join ...

keep going until you get 2 rows.

After that you can reformat the code like this and get rid of all the unnecessay brackets - will be a lot easier to detect errors.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources