Question 113574 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Query to find records belongs to two states

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
Tags:

3

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search