Select multiple rows - SOLVED

1

I have a database with two tables

Table 1: Members
Member1
Member2
Member3
Member4

Table 2: Groups
Member1 | Group1
Member1 | Group2
Member2 | Group1
Member3 | Group1
Member3 | Group2
Member3 | Group3
Member4 | Group1
Member4 | Group2
Member4 | Group3
Member4 | Group4

I need a SQL query that can list all members that are in Group1 and Group3 (at the same time, not all member Group1 and all members in Group3).

I should be able to select as many groups I want and get the members that belong to all groups selected.

Please advice.

2 
SELECT
	MemberName
FROM
	Groups g
	INNER JOIN (VALUES ('Group1'),('Group2')) v(groupname) ON
		v.groupname = g.groupname
GROUP BY
	g.MemberName
HAVING
	COUNT(DISTINCT g.groupname) = 2; 
-- 2 is the number of groups in which membership is requried.
-- i.e., the groups listed in the values clause above.
3

Thank you for your reply. I have problem running this query, I get syntax error for the VALUES statement. How can you join the two tables using groupname when it only exists in on of the tables?

4

What is the error you are getting? What version of SQL Server are you running (SELECT @@VERSION will tell you). You can modify the query like shown below to avoid values clause

SELECT
	MemberName
FROM
	Groups g
WHERE
	g.GroupName IN ('Group1','Group2')
GROUP BY
	g.MemberName
HAVING
	COUNT(DISTINCT g.groupname) = 2;
5

Thanks again. I should have told you that I'm using a SQL CE 4 database. The first part of your script is working, but it is trowing an error on the HAVING part of the script. The DISTINCT function is working for CE, but not in the same way. Not sure how to use it together with =2 in this case.

6

I've changed the TAG on this thread from "SQL2008" to SQLCE_V4" (because I was not sure if you have permission to do that, or not)

7

Thanks for changing the TAG, sorry about the confusion.

1 Like
8

I got it to work with the following query:

SELECT member 
FROM groups
WHERE group IN ('group1', 'group3')
GROUP BY member
HAVING COUNT(*) = 2