[SOLVED] Filtering column customer ID that has multiple product ID in SQL

Filtering column customer ID that has multiple product ID in SQL

I have a data like below:

cust_id	acc_id	prd_id
cust_a	id_a1	prdA
cust_a	id_a2	prdA
cust_a	id_a3	prdB
cust_a	id_a4	prdC
cust_b	id_b1	prdA
cust_c	id_c1	prdA
cust_d	id_d1	prdB
cust_d	id_d2	prdC
cust_e	id_e1	prdC
cust_f	id_f1	prdA
cust_f	id_f2	prdB
cust_g	id_g1	prdB
cust_g	id_g2	prdB

From the table we can see that cust_a,cust_d and cust_f have multiple product. However cust_a and cust_g has one product with different acct_id while the other only have one product and one acct_id.

My objective is to have only customer that have multiple product. Expected table is as below:

cust_id	prd_id
cust_a	prdA
cust_a	prdB
cust_a	prdC
cust_d	prdB
cust_d	prdC
cust_f	prdA
cust_f	prdB

This is my code, and I think there might be simpler way to do it or any amendment to my code.

select cust_id, prd_id
from acct_tbl
where exists (select cust_id, count (*)
              from acct_tbl 
              where prd_id is in ('prdA','prdB','prdC')
              group by cust_id
              having count (*) >=1)

where prd_id is in ('prdA','prdB','prdC');

Solution

You could express this as:

SELECT cust_id, prd_id
FROM acct_tbl
WHERE cust_id IN (
    SELECT cust_id
    FROM acct_tbl
    GROUP BY cust_id
    HAVING MIN(prd_id) <> MAX(prd_id)
)
ORDER BY cust_id, prd_id;

Another way, using window functions:

WITH cte AS (
    SELECT t.*,
        MIN(prd_id) OVER (PARTITION BY cust_id) AS min_prd_id,
        MAX(prd_id) OVER (PARTITION BY cust_id) AS max_prd_id
    FROM acct_tbl t
)

SELECT cust_id, prd_id
FROM cte
WHERE min_prd_id <> max_prd_id
ORDER BY cust_id, prd_id;