returning multiple copies - 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

returning multiple copies

Author

Topic

jamez36
Starting Member

3 Posts

Posted - 2006-01-13 : 17:19:10

I'm getting 3 copies of the result set expected, could someone take a look and tell me why. I know I covered this in school, but I can't remember the issue. Thanks

SELECT P.Quantity as Qty,
P.ItemID,
P.VendorCode,
P.Descr as Description,
P.UnitPrice as Price,
P.Amount,
I.Freight,
Rcvd = 0,
I.QtyRcvd as Ship
FROM PurchaseOrderItems P, ReceivedItems I, Received R
WHERE P.POID = R.POID AND R.IntRcdID = I.PRID AND P.POID = 193

jamez36
Starting Member

3 Posts

Posted - 2006-01-13 : 18:33:43

I got it, thanks anyway. Just needed final comparison in WHERE

SELECT P.Quantity as Qty,
P.ItemID,
P.VendorCode,
P.Descr as Description,
P.UnitPrice as Price,
P.Amount,
I.Freight,
Rcvd = 0,
I.QtyRcvd as Ship
FROM PurchaseOrderItems P, ReceivedItems I, Received R
WHERE P.POID = R.POID AND R.IntrcdID = I.PRID AND P.POID = 193
AND I.ItemID = P.ItemID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-13 : 22:43:04

It is better to write your query this way using INNER JOIN

SELECT P.Quantity as Qty,
       P.ItemID,
       P.VendorCode,
       P.Descr as Description,
       P.UnitPrice as Price,
       P.Amount,
       I.Freight,
       Rcvd = 0,
       I.QtyRcvd as Ship
FROM   PurchaseOrderItems P INNER JOIN Received R
       ON  P.POID     = R.POID 
       INNER JOIN ReceivedItems I
       ON  R.IntrcdID = I.PRID
       AND P.ItemID   = I.ItemID
WHERE  P.POID = 193

-----------------
'KH'
if you can't beat them, have someone else to beat them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources