turbo88 turbo88 - 7 months ago 24
SQL Question

SQL Self joining query

I have a table in the following format.

Id Orig_Id Type
11 1111 Internal
12 1111 Internal
13 1111 Internal
14 1112 External
15 1112 Internal


I want to retrieve all the Orig_Id's which has "ONLY" the Type as Internal. The query should return 1111.

Answer

Use HAVING:

SELECT Id
FROM tbl
GROUP BY Orig_Id
HAVING 
    SUM(CASE WHEN Type <> 'Internal' THEN 1 ELSE 0 END) = 0
    AND SUM(CASE WHEN Type = 'Internal' THEN 1 ELSE 0 END) > 0

The first condition makes sure that the Orig_Id has no rows with Type = Internal. The second one makes sure that it has at least one Internal.

ONLINE DEMO

Comments