Select statement that will return one record based on multiple records

Question

Have seen similar questions but not exactly the same. I have a table with multiple ID's and want to do a select statement that fits all the PIDs and return the ID once.

+----+-----+-----+-------+
| ID | PID | DET | DETOP |
+----+-----+-----+-------+
|  1 | 123 | TR  | EQ    |
|  1 | 234 | US  | EQ    |
|  1 | 536 | L   | EQ    |
|  2 | 123 | TR  | EQ    |
|  2 | 234 | US  | EQ    |
|  2 | 536 | D   | EQ    |
+----+-----+-----+-------+

In other words, if 123=TR and 234=US and 536=L (hope this makes sense) return 1 and not 1,1,1.

I can't do a

SELECT ID 
FROM MYTABLE 
WHERE PID = 123 
  AND DET = 'TR' 
  AND PID = 234 
  AND DET = 'US' 
  AND PID = 536 
  AND DET = 'L'

As that will just return zero results. What would be the best approach for this?

Answer 1

I think you want a HAVING?

SELECT ID
FROM dbo.YourTable
GROUP BY ID
HAVING COUNT(CASE WHEN PID = 123 AND DET = 'TR' THEN 1 END) > 0
   AND COUNT(CASE WHEN PID = 243 AND DET = 'US' THEN 1 END) > 0
   AND COUNT(CASE WHEN PID = 536 AND DET = 'L' THEN 1 END) > 0;