CODEWITHSUNDEEP
sqlsql-server-2008
Red Devil
Red Devil

Reputation: 2403

Joining same table getting duplicate rows for some ID

I have a table test 

ID       Status     

1       A     
1       C     
2       A   
3       A   
4       A  
4       C  
4       A  
4       C  
5       A


Desire Output

ID      Status  
1       A   
1       C     
4       A   
4       C   
4       A   
4       C

` I tried this

 select * from test 
join
    (
        select id from test t
        where status='c'
    ) b
on b.id=test.id

It works fine for ID=1 wherein it gives me 2 row, but for id=4 it gives me 8 rows I don't know what I am doing wrong

Upvotes: 2

Views: 2493

Answers (4)

shove
shove

Reputation: 79

If you use count in your inner SELECT you do not need to filter for any specific Status:

SELECT t1.ID, t1.Status
FROM [test] t1
INNER JOIN
(
    SELECT ID
    FROM [test]
    GROUP BY ID
    HAVING COUNT(*) > 1
) t2
    ON t1.ID = t2.ID

Upvotes: -1

Kinchit Dalwani
Kinchit Dalwani

Reputation: 398

If you want to go for desired output then try using count:

 SELECT 
    t1.ID, t1.Status
 FROM 
    test t1
 GROUP BY
    t1.ID, t1.Status
 Having
    COUNT(t1.ID) > 1

Upvotes: -1

Veljko89
Veljko89

Reputation: 1953

You get 8 rows, because your ID 4 has more 'Cs' then just 1

If you just run 

    select id from test t
    where status='c'

You will get 

    1, 4, 4

As result

So in your inner select try with 

    select DISTINCT id from test t
    where status='c'

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522731

The only issue with your query is that the subquery currently will return multiple IDs having the C status. One option here is to use GROUP BY ID in the subquery to restrict each ID to appear only once.

SELECT t1.ID, t1.Status
FROM test t1
INNER JOIN
(
    SELECT ID
    FROM test
    WHERE Status = 'C'
    GROUP BY ID
) t2
    ON t1.ID = t2.ID

Upvotes: 3

Related Questions