Group two database tables

Question

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 in 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 at the same time.

I'm using SQL CE 4 database.

Please advice.

Answer 1

You need only one of the tables to list all members. You would only need the members table to retrieve additional information about each member in a single query (which was not the question).

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

Assuming a unique key on the combination of a member and a group in the groups table.