SQL Query to find records belongs to two states

hellopa1 0
Viewed 61
cust_id state
1 CA
2 IL
3 SC
1 PA
3 IA
4 MO
5 VA
4 NY

Can any one please advise on SQL Query that return the cust_id that belong to two states as below:

The output should be

cust_id state
1 CA
1 PA
3 SC
3 IA
4 NY
4 MO
hql mysql sql
3 Answers

Try the following query

Solution 1

Select * From yourtable
Where Cust_Id In (Select cust_id From yourtable Group By cust_id Having 
Count(*) = 2) Order By cust_id,state

Solution 2

With T1 As 
(Select cust_id From yourtable Group By cust_id Having Count(*) = 2) 
Select T2.* From yourtable T2 Join T1 On T1.cust_id = T2.cust_id O 
Order By T2.cust_id,T2.state
Uday Dodiya0 
SELECT tmp.*
   FROM tmp
 INNER JOIN (
    SELECT cust_id
        ,COUNT(STATE) s_count
    FROM tmp
    GROUP BY [cust_id]
) sub 
ON tmp.cust_id = sub.cust_id
 WHERE sub.s_count = 2
 ORDER BY cust_id
    ,STATE
RuchDi0

One simple approach would use a COUNT window function, that will assign the amount of times each "cust_id" occurs in your table. Once you get this value, you can filter out rows whose count is smaller than 2.

WITH cte AS (
    SELECT *, COUNT(cust_id) OVER(PARTITION BY cust_id) AS cnt 
    FROM tab
)
SELECT cust_id,
       state
FROM cte 
WHERE cnt > 1

Check the demo here.

userQZpQ0
Related