Exclude data from Query

Question

I have an Inventory table and I want to return  only certain records (and exclude others) when a condition exists, however that condition isn't just on a record by record case. The 3 columns that will be tested are PalletID, Location & SKU. So the condition I want to test for is When the PalletID = Location I want to exclude all SKUs in the inventory table even those in a Location where the PalletID<>Location. Or stating it another way to only include SKUs where there is no instance of a record where the PalletID = Location.  For example I have the following data:

PalletID           Location      SKU

1                     1                 ABC           (PalletID and Location are equal)

2                     A1               ABC           (PalletID and Location are not equal)

3                     A2               ABC           (PalletID and Location are not equal)

4                     A4               XYZ

5                     A5              LMN

The reslut of the Query should show

PalletID           Location      SKU

4                     A4               XYZ

5                     A5              LMN

Since SKU ABC has at least one instance where the PalletID = Location none of the ABC records should show in the result set.

Thanks in advance.

Gary

Answer 1

You could use the EXISTS operator:

select *
from T
where not exists (select * from T as R where R.sku = T.sku and R.palletid = R.location);

---

AMB

Some guidelines for posting questions...

Answer 2

A small modification in the above query,

select *
from T T
where not exists (select * from T as R where R.sku = T.sku and  R.location= cast (R.palletid as  varchar(3)));

---

Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 3

Thanks Hunchback & Sarat, that did the trick. With a couple modifications I got the data I was looking for.

Answer 4

Try like this,

declare @table table (PalletID int, Location nvarchar(max), SKU nvarchar(max))
insert into @table
select 1,'1','ABC'  union
select 2,'A1','ABC' union
select 3,'A2','ABC' union
select 4,'A4','XYZ' union
select 5,'A5','LMN'

select * from @table
where SKU not in (select SKU from @table where cast(PalletID as varchar(max)) = Location)

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you