SQL Query to get shared records

Question

I have a table below:

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

Using the sql operator, I want to get all the username available in both groups 2,3

In this case, only Venkat is the username available in both groupid 2 and 3

Please help me

Answer 1

Try the following:

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

Check SQL FIDDLE DEMO

OUTPUT

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

Answer 2

An alternative approach using the simple JOIN;

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

SQLfiddle for testing with .