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 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.

ola
  • 11
  • 2

1 Answers1

0

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.

pvoosten
  • 3,247
  • 27
  • 43
  • It works, I understand what you mean with your comment. Just had some problem to get this to work, but your solution is great. Thanks! – ola Nov 29 '16 at 20:42
  • @ola: You can mark it as an answer and upvote. Welcome to StackOverflow! – pvoosten Dec 06 '16 at 20:50