Query Help Please

[Guest]

Can someone help me with a query.

I have a table like below

ItemNo CostType
12345 a
12345 b
54321 a
54321 c
09876 b
09876 c

I need a query that will pull the items that do not have a CostType b record.

In the above example the query would only return 54321.

When I try to write it, I get each record that is not a b (ie all of them in
the above example)

Thanks!

 

[John Spencer]

SELECT Distinct ItemNo
FROM [Your Table] as YT
WHERE NOT EXISTS
(SELECT *
FROM [Your Table] as T
WHERE T.ItemNo = YT.IemNo and CostType = "B")

The only problem with the above is that it could be slow with a large table.
Not exists is quite slow.

Two query solution is to build first query that gets all the ITemNo records
that do have a costtype of B and then use the unmatched query wizard to
return those that don't.

** IF** your table and field names don't have spaces or other special
characters you can do this all in one query.

SELECT Distinct ItemNo
FROM YourTable LEFT JOIN
(SELECT ItemNo
FROM YourTable
WHERE CostType = "B") as T
ON YourTable.ItemNo = T.ItemNo
WHERE T.ItemNo is Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

SELECT DISTINCT ItemNo
FROM YourTable
WHERE ItemNo NOT IN
(SELECT ItemNo
FROM YourTable
WHERE CostType = "b")
ORDER BY 1;

Change YourTable to the actual table name.

If you have a lot of records, this could be slow. Indexing the ItemNo and
CostType fields could speed things up.

 

[Guest]

I forgot an old rule: If an IN statement is slow, try an EXISTS and visa versa.


SELECT DISTINCT A.ItemNo
FROM YourTable AS A
WHERE A.ItemNo NOT EXISTS
(SELECT "X"
FROM YourTable as CT
WHERE CT.ItemNo = A.ItemNo
AND CT.CostType = "b")
ORDER BY 1;