CODEWITHSUNDEEP
mysqlsql
Mamadum
Mamadum

Reputation: 540

MySQL select on results of previous statement

I'm rather new to SQL I came across this problem: I have relationship table where person has power

A has X
A has Z
B has X
B has Y
C has Y
D has Z

I want to select all persons with powers X and Y which is in this case B. Of course I will have a more complex power combination.

Upvotes: 0

Views: 95

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

This is called relational division, here is one way to do this:

SELECT person
FROM Powers
WHERE power IN ('x', 'y')
GROUP BY person
HAVING COUNT(power) = 2

Upvotes: 1

John Woo
John Woo

Reputation: 263723

Do something like this,

SELECT person
FROM tableName
WHERE power IN ('x', 'y')
GROUP BY Person
HAVING COUNT(*) = 2

if unique constraint was not enforce of power on every person, you need to use DISTINCT

SELECT person
FROM tableName
WHERE power IN ('x', 'y')
GROUP BY Person
HAVING COUNT(DISTINCT power) = 2

Upvotes: 1

Related Questions