Exclude record if in 2 locations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darrell Childress - 11 Nov 2005 20:01 GMT

I have a table that has the following fields
item
location
qty

Many items are in multiple locations (SBC and COMP). I am trying to
write a query that will produce all records where [location] = COMP and
only those records (items) at location SBC where there is not a record
for that item at location COMP. By the way, it might be worth mentioning
that every item has an entry for location SBC, but only certain items
have an entry for location COMP. In the example below, I would want to
exclude record 1 because it also exists in location COMP (record 2). I
would want to INCLUDE record 3 because it does not exist in location COMP.

Item    Location    Qty
123    SBC        0
123    COMP        5
254    SBC        6
365    COMP        4

As I'm writing this, it occurred to me that I may have to do this in 2
queries, the first simply setting the criteria of location = COMP and
then do an unmatched query???..and then possibly a UNION query of the 2
Thanks for any help,
Darrell

Reply to this Message

Tom Ellison - 11 Nov 2005 20:29 GMT

Dear Darrell:

I'm thinking of joining the table to a query of the same table limiting
itself to Location = COMP, joined on Item. Make this a LEFT JOIN. Then,
when there is NO row for that same item at Location COMP, values from the
joined query will be null.

SELECT Item, Location, Qty
FROM YourTable T
LEFT JOIN
(SELECT * FROM YourTable
WHERE Location = "COMP") Q
ON Q.Item = T.Item
WHERE T.Location = "SBC"
AND Q.Location IS NULL

For this to work, I have assumed that Location is never NULL in the table.

Note that this will not include items that exist in location COMP but have a
zero quantity there. Your post did not mention this possibility. If you
wish to include such items, change the last line to:

AND Nz(Q.Qty, 0) <> 0

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

>I have a table that has the following fields
> item
[quoted text clipped - 21 lines]
> Thanks for any help,
> Darrell

Reply to this Message