Oracle SQL: select from a set of orders only those that contain certain types

Question

I want to select from a set of orders only those that are marked with certain types.

Example:

Let's assume I have a table X.

Order   Type
123     A
123     B
123     C
234     A
234     C
345     B
456     C
456     A
567     A
567     B

In my case I want to select only those orders that contain both A and B:

Order   Type
123     A
123     B
123     C
567     A
567     B

My work so far:

WITH FIRST AS (
SELECT DISTINCT ID
FROM X 
WHERE TYPE = 'A'),

SECOND AS (
SELECT DISTINCT ID
FROM X 
WHERE TYPE = 'B'
)
SELECT *
FROM X 
WHERE Order IN (    SELECT DISTINCT FIRST.ID
                    FROM FIRST
                    JOIN SECOND 
                    ON FIRST.ID = SECOND.ID);
                

However, I don't like this solution. Is there a better approach?

Answer 1

Use an analytic function:

SELECT order_no,
       type
FROM   (
  SELECT x.*,
         COUNT( CASE type WHEN 'A' THEN 1 END ) OVER ( PARTITION BY order_no )
           AS num_a,
         COUNT( CASE type WHEN 'B' THEN 1 END ) OVER ( PARTITION BY order_no )
           AS num_b
  FROM   x
)
WHERE  num_a > 0
AND    num_b > 0;

Which, for the sample data:

CREATE TABLE x ( Order_no, Type ) AS
SELECT 123, 'A' FROM DUAL UNION ALL
SELECT 123, 'B' FROM DUAL UNION ALL
SELECT 123, 'C' FROM DUAL UNION ALL
SELECT 234, 'A' FROM DUAL UNION ALL
SELECT 234, 'C' FROM DUAL UNION ALL
SELECT 345, 'B' FROM DUAL UNION ALL
SELECT 456, 'C' FROM DUAL UNION ALL
SELECT 456, 'A' FROM DUAL UNION ALL
SELECT 567, 'A' FROM DUAL UNION ALL
SELECT 567, 'B' FROM DUAL;

Outputs:

ORDER_NO | TYPE
-------: | :---
     123 | A   
     123 | B   
     123 | C   
     567 | A   
     567 | B   

db<>fiddle here