CODEWITHSUNDEEP
sqlms-accessrows
TripleAntigen
TripleAntigen

Reputation: 2301

Select a value in multiple rows based on values in a specific field

My table has rows that list object types and the shelves that some can be found on, like this:

1,   wrench,   shelf1
2,   wrench,   shelf2
3,   hammer,   shelf2
4,   hammer,   shelf3
5,   pliers,   shelf1
6,   nails,   shelf3
7,   nails,   shelf4

I am trying to decide how to create a query that will return any objects that can be found on shelf1 but not on shelf2.

I know everybody loves code on SO, but I wont be at work for a few days and I don't have the failed attempts I have made already. I did discover that Access doesn't have EXCEPT, and doesn't support aliases for queries, so I can do a query and run another query against the results. I cannot write temp tables into the existing database, with INSERT INTO.

Any advice on how to get started would be much appreciated!

Upvotes: 0

Views: 58

Answers (3)

Daniel André
Daniel André

Reputation: 1258

Here is a simple query for that problem. You verify if an object in shelf1 is not in the table of all the objects in shelf2 

SELECT *
    FROM Table
    WHERE shelf = 'shelf1' AND object NOT IN 
    (SELECT object FROM Table WHERE shelf='shelf2')

Upvotes: 1

GarethD
GarethD

Reputation: 69819

If you only need the product name you can use GROUP BY/HAVING:

SELECT  Name
FROM    T
WHERE   Shelf IN ('shelf1', 'shelf2')
GROUP BY Name
HAVING SUM(IIF(Shelf = 'Shelf2', 1, 0)) = 0;

The having just uses a conditional sum to exclude any that have a record on shelf2.

Finally, just to extend JNevill's answer slightly, the subqueries are not necessary, you can do this in a slightly simpler way:

SELECT  t1.ID, t1.Name, t1.Shelf
FROM    T AS t1
        LEFT JOIN T AS t2
            ON t.Name = t2.Name
            AND t2.Shelf = 'Shelf2'
WHERE   t1.Shelf = 'Shelf1'
AND     t2.ID IS NULL;

Upvotes: 1

JNevill
JNevill

Reputation: 50308

You can make a subquery for both Shelf 1 and Shelf 2. Then LEFT JOIN the Shelf1 subquery to the Shelf2 subquery on the Product name. Then only take records where Shelf2 is null.

SELECT
    Shelf1.*
FROM
    (SELECT [ID], [Product], [Location] FROM <table> WHERE [location]="Shelf1") as Shelf1
    LEFT OUTER JOIN
        (SELECT [Product] FROM <table> WHERE [location]="Shelf2") as Shelf2 ON
            Shelf1.Product = Shelf2.Product
WHERE
    Shelf2.Product IS NULL

Upvotes: 2

Related Questions