CODEWITHSUNDEEP
phpmysqlsql
user2875157
user2875157

Reputation: 140

SQL QUERY multiple search in one row to find data from another row in the same table

I need some help with some code

I have a database table called "stuff" and I have this info:

+------+-------------+---------------------+
| id   | member_id   |     group_id        |
+------+-------------+---------------------+
| 1    |      11     |         aa          |
+------+-------------+---------------------+
| 2    |      22     |         aa          |
+------+-------------+---------------------+
| 3    |      33     |         aa          |
+------+-------------+---------------------+
| 4    |      44     |         bb          |
+------+-------------+---------------------+
| 5    |      55     |         bb          |
+------+-------------+---------------------+
| 6    |      66     |         bb          |
+------+-------------+---------------------+

I need to find the group id if I search all 3 members from one group

Something like: 

SELECT group_id 
FROM stuff 
WHERE member_id=11 and member_id=22 and member_id=33

I know the query it is not valit but I don`t know how to make it valid.

Thank you very much.

Upvotes: 1

Views: 591

Answers (1)

John Woo
John Woo

Reputation: 263723

The problem is called Relational Division.

SELECT  group_id
FROM    stuff
WHERE   member_id IN (11,22,33)
GROUP   BY group_id
HAVING  COUNT(*) = 3

if member_id is not unique for every group_id, you need to have DISTINCT in order to count only unique values.

SELECT  group_id
FROM    stuff
WHERE   member_id IN (11,22,33)
GROUP   BY group_id
HAVING  COUNT(DISTINCT member_id) = 3

More variations on this link:

Upvotes: 2

Related Questions