Reputation: 2412
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
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
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