SQL Query to get common records

Question

I have a table as below

ID  Username GroupID
1   venkat     2
2   venkat     3
3   ramu       1
4   ramu       2

Using the sql statement I want to retrieve all username's that are available in both the groupids 2,3

In this case only Venkat is the username that's available in both groupid 2 and 3

Kindly help me

Saharsh Shah · Answer 1 · 2014-01-18T04:28:10.233

10

Try this:

SELECT userName
FROM tableA 
WHERE groupId IN (2, 3)
GROUP BY userName 
HAVING COUNT(DISTINCT groupId) = 2;

OUTPUT

| USERNAME |
|----------|
|   venkat |

edited Jan 18 '14 at 04:28

answered Dec 24 '13 at 12:43

Saharsh Shah

1

what if the a user is present twice for the same group id (for example user Pat is present twice for group id 2) - guess the present query wont work then – Scorpion Dec 24 '13 at 12:45
1

@Scorpion It will work because I had counted DISTINCT groupId for each user – Saharsh Shah Dec 24 '13 at 12:48
1

@AmitAgrawal Check my fiddle demo. The Query will work – Saharsh Shah Dec 24 '13 at 12:49
In the sample data the user venkat is repeating twice only, but we need only if the user is present in both the groups we can't limit the query to HAVING COUNT(DISTINCT groupId) = 2 because we dont know if it is 2 or 3 or someother number – Tarak Dec 24 '13 at 12:49
@Tarak Check the fiddle demo. I had already aded where condition which will fetch only records for two groups (2, 3) and then I had used having clause with distinct groupId so it will work as per your need – Saharsh Shah Dec 24 '13 at 12:51
1. What is the purpose of the HAVING clause? 2. "groupid 2 and 3". Get rid of the HAVING clause and replace the IN with an AND. – Bill Stidham Dec 24 '13 at 12:51
@Scorpion Not a problem – Saharsh Shah Dec 24 '13 at 12:54
1

@BillStidham Since there are 2 separate rows for the two groups, an `AND` won't do since it matches only if a single row contains both groupid's. – Joachim Isaksson Dec 24 '13 at 12:55
@JoachimIsaksson Yep, I missed that. He'll need a self join too. – Bill Stidham Dec 24 '13 at 12:58

score 7 · Answer 2 · answered Dec 24 '13 at 12:50

7

An alternate approach using a plain JOIN;

SELECT DISTINCT t1.username 
FROM MyTable t1 JOIN MyTable t2
  ON t1.username = t2.username AND t1.groupid=2 AND t2.groupid=3;

answered Dec 24 '13 at 12:50

Joachim Isaksson

its a much cleaner and better approach +1 – R R Dec 24 '13 at 12:51
What if i have multiple groupid's like 1,2,3,4,5..10 .. can you help me giving a much flexible query – Tarak Dec 24 '13 at 13:17
@Tarak For that you have to use self join the table multilple times. Means if you have 5 Groups than you have join your table 5 times. – Saharsh Shah Dec 24 '13 at 13:22

2 Answers2