Help with SQL Query

[johnhugh]

Hi,

I'm working on SQL 2005.
I have 2 tables PODetails and VendorItemNumbers.

PODetails
ItemNo | VendorItemNo
123 | ABC
456 | XYZ

VendorItemNumber
ItemNo | VendorItemNo
123 | ABC
123 | DEF
456 | GHY

My query result should only show the item numbers which have an incorrect vendor item number.
In the above example it should only return 456 - GHY because this combination does not exist in table VendorItemNumber.
My query however returns 456 - GHY AND 123 - DEF.

SELECT * FROM PODetails INNER JOIN VendorItemNumber ON PODetails.Itemno = VendorItemNumber.ItemNo WHERE PODetails.VendorItemNo <> VendorItemNumber.VendorItemNo

How do I get it to only show those combination that do not exist in table VendorItemNumber?

Thanks for any help.

 

[r937]

SELECT PODetails.* 
  FROM PODetails 
LEFT OUTER 
  JOIN VendorItemNumber 
    ON VendorItemNumber.ItemNo = PODetails.Itemno
   AND VendorItemNumber.VendorItemNo = PODetails.VendorItemNo 
 WHERE VendorItemNumber.ItemNo IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JonFer]

SELECT PODetails.*
FROM PODetails LEFT JOIN VendorItemNumber ON
PODetails.Itemno = VendorItemNumber.ItemNo And
PODetails.VendorItemNo = VendorItemNumber.VendorItemNo
WHERE VendorItemNumber.VendorItemNo Is Null

You want to use a LEFT join on both key fields to see the records that have an invalid combination of values.

 

[MarkSweetland]

In the above example it should only return 456 - GHY

You'll have to reverse the logic a bit to get the row in VendorItemNumber that is the 'correct' combination to use:

set nocount on 

declare @PODetail TABLE (itemNo int, VendorItemNo varchar(3))

insert into @PODetail(itemNo, VendorItemNo)
select 123, 'ABC'
union all 
select 456,  'XYZ'

declare @VendorItemNumber TABLE (itemNo int, VendorItemNo varchar(3))
insert into @VendorItemNumber(itemNo, VendorItemNo)
select 123,'ABC'
union all 
select 123, 'DEF'
union all 
select 456, 'GHY'

select * 
from @VendorItemNumber c
where exists (
	--mismatched item/vendor
	select * 
	from @PODetail a
	where not exists(
		select 1 
		from @VendorItemNumber b 
		where (b.ItemNo = a.Itemno AND b.VendorItemNo = a.VendorItemNo)
	)
	-- and the itemNo used in a PO
	and c.itemNo = a.itemNo
)

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[SQLBill]

it should only return 456 - GHY because this combination does not exist in table VendorItemNumber.

But 456 - GHY DOES exist in VendorItemNumber. 456 - XYZ does not exist in VendorItemNumber.

So what do you REALLY want? The combinations that are in PODetails but not in VendorItemNumber (would return 456 - XYZ)? Or the combinations that are in VendorItemNumber but not in PODetails (would return 123 - DEF and 456 - GHY).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875