CODEWITHSUNDEEP
sqlms-access
Glenn G
Glenn G

Reputation: 667

SQL To Return Data set Where Multiple Conditions Are Met

I have the following MS-Access table:

╔═══════════╦══════════════╗
║ colPartNo ║ colOperation ║
╠═══════════╬══════════════╣
║ 1         ║ A            ║
╠═══════════╬══════════════╣
║ 1         ║ B            ║
╠═══════════╬══════════════╣
║ 1         ║ C            ║
╠═══════════╬══════════════╣
║ 2         ║ B            ║
╠═══════════╬══════════════╣
║ 2         ║ C            ║
╠═══════════╬══════════════╣
║ 3         ║ C            ║
╠═══════════╬══════════════╣
║ 3         ║ D            ║
╠═══════════╬══════════════╣
║ 3         ║ E            ║
╠═══════════╬══════════════╣
║ 4         ║ A            ║
╠═══════════╬══════════════╣
║ 4         ║ B            ║
╠═══════════╬══════════════╣
║ 4         ║ C            ║
╠═══════════╬══════════════╣
║ 4         ║ D            ║
╠═══════════╬══════════════╣
║ 5         ║ A            ║
╠═══════════╬══════════════╣
║ 5         ║ B            ║
╠═══════════╬══════════════╣
║ 5         ║ C            ║
╠═══════════╬══════════════╣
║ 6         ║ B            ║
╠═══════════╬══════════════╣
║ 6         ║ C            ║
╚═══════════╩══════════════╝

What I'm trying to do and can't seem to wrap my head around, is to return a dataset that contains only the distinct part numbers where the part number has operations A, B, & C. The part number may have other operations but it MUST have all three of these. I can get a distinct list of the part numbers but it includes parts that do not have all three of the operations, using the statement below:

SELECT DISTINCT tblPart_Info.Part_No, tblPart_Info.Operation
FROM tblPart_Info
WHERE (((tblPart_Info.Operation)="A" Or (tblPart_Info.Operation)="B" Or (tblPart_Info.Operation)="C"))
ORDER BY tblPart_Info.Part_No;

The dataset that I'm looking to get back would be part numbers 1, 4, & 5

Upvotes: 1

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can do this with conditional aggregation in the having clause:

SELECT pi.Part_No, tblPart_Info.Operation
FROM tblPart_Info as pi
GROUP BY pi.Part_No
HAVING SUM(IIF(pi.Operation = "A", 1, 0)) > 0 AND
       SUM(IIF(pi.Operation = "B", 1, 0)) > 0 AND
       SUM(IIF(pi.Operation = "C", 1, 0)) > 0;

This returns the part numbers. If you want the original rows, use JOIN, IN, or EXISTS to connect this to the original table.

Upvotes: 3

Related Questions