CODEWITHSUNDEEP
sqlms-access
Matt Hall
Matt Hall

Reputation: 2412

MS-Access - Query to return each subset of records based on criteria

How would I go about writing a query that results in the following...

Let's say my dataset looks this:

| PersonId | LastName | FirstName | ProductType | ProductValue |
|----------|----------|-----------|-------------|--------------|
| P0000001 |  Bloggs  |    Joe    |      1      |      £15     |
| P0000001 |  Bloggs  |    Joe    |      1      |      £20     |
| P0000001 |  Bloggs  |    Joe    |      2      |      £10     |
| P0000001 |  Bloggs  |    Joe    |      3      |      £5      |
| P0000002 |    Doe   |   Jane    |      2      |      £25     |
| P0000002 |    Doe   |   Jane    |      3      |      £60     |
| P0000003 |   Brown  |    Jim    |      1      |      £20     |
| P0000003 |   Brown  |    Jim    |      3      |      £5      |

Where a PersonId has at least 1 occurance of ProductType = 1 in any of that PersonId's set of records, return all records for that PersonId (even if the other records returned from the set have a ProductType that is not 1).

So the above dataset would be filtered like so:

| PersonId | LastName | FirstName | ProductType | ProductValue |
|----------|----------|-----------|-------------|--------------|
| P0000001 |  Bloggs  |    Joe    |      1      |      £15     |
| P0000001 |  Bloggs  |    Joe    |      1      |      £20     |
| P0000001 |  Bloggs  |    Joe    |      2      |      £10     |
| P0000001 |  Bloggs  |    Joe    |      3      |      £5      |
| P0000003 |   Brown  |    Jim    |      1      |      £20     |
| P0000003 |   Brown  |    Jim    |      3      |      £5      |

Jane Doe didn't have any records with ProductType = 1 so all her records were filtered-out. Both Joe Bloggs and Jim Brown have at least 1 record with ProductType = 1 so all their records are returned in the query.

Upvotes: 0

Views: 94

Answers (2)

Edper
Edper

Reputation: 9322

You could use GROUP BY and HAVING clause:

SELECT PersonID, LastName, Firstname
FROM tbl
WHERE ProductType=1
GROUP BY PersonID, LastName, FirstName
HAVING COUNT(*) >=1

And then you use IN clause:

SELECT * FROM tbl
WHERE PersonID IN (
  SELECT PersonID
  FROM tbl
  WHERE ProductType=1
  GROUP BY PersonID
  HAVING COUNT(*) >=1
)

Upvotes: 2

Arion
Arion

Reputation: 31249

You could do it with an EXISTS. Like this:

SELECT 
    * 
FROM 
    yourTable as tbl
WHERE EXISTS
    (
        SELECT
            NULL
        FROM
            yourTable AS tbl1
        WHERE
            yourTable.PersonId=tbl.PersonId
            AND tbl1.ProductType=1
    )

This will get you the output:

P0000001    Bloggs  Joe 1   £15
P0000001    Bloggs  Joe 1   £20
P0000001    Bloggs  Joe 2   £10
P0000001    Bloggs  Joe 3   £5
P0000003    Brown   Jim 1   £20
P0000003    Brown   Jim 3   £5

Upvotes: 2

Related Questions