SQL Query to find records belongs to two states-CodePudding

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

CodePudding user response：

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

CodePudding user response：

SELECT * FROM yourtable t1
JOIN
(SELECT cust_id,COUNT(DISTINCT state) AS cnt 
   FROM yourtable  GROUP BY cust_id HAVING cnt =2) t2
ON t1.cust_id=t2.cust_id
ORDER BY t1.cust_id,t2.state

CodePudding user response：

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