CODEWITHSUNDEEP
mysql
jon
jon

Reputation: 1591

MYSQL select based on the selects

You're help would be much appreciated... If I have the following table and sample data... myGroupTable


group_Id : user_Id
1 : 3
1 : 7
1 : 100
2 : 3
2 : 7
2 : 100
2 : 104
4 : 42
4 : 98
4 : 13

I would like a sql statement that would... Return a group_Id that has exactly the specified user_Id's in them. eg... is there a group_Id that has User_Id's 3, 7 and 100 answer: group_id 1. Please note that I dont want it to return a group_Id 2, as that also has a user_Id of 104 in it... Kind regards J

Upvotes: 3

Views: 156

Answers (4)

Ivan
Ivan

Reputation: 2262

another solution:

SELECT group FROM tbl WHERE group_id NOT IN (SELECT DISTINCT group_id FROM tbl WHERE user_id NOT IN(3,7,100)) AND user_id IN (3,7,100);

Upvotes: 0

ajreal
ajreal

Reputation: 47331

select group_id,
sum(
  case user_id in(3,7,100)
  when 1 then 1
  else -99
  end
) as must_be_3
from group_user
group by group_id
having must_be_3=3;

Upvotes: -1

Bill Karwin
Bill Karwin

Reputation: 562911

Here's another alternative:

SELECT group_id, GROUP_CONCAT(user_id ORDER BY user_id) AS user_id_list
FROM group_user
GROUP BY group_id
HAVING user_id_list = '3,7,100'

Upvotes: 1

Robert Martin
Robert Martin

Reputation: 17177

SELECT
    group_Id,
    SUM(
        IF user_Id = 3 THEN 1
        ELSEIF user_Id = 7 THEN 2
        ELSEIF user_Id = 100 THEN 4
        ELSE 8
    ) AS bits
FROM myGroupTable
GROUP BY group_Id
HAVING bits=7

This assumes that you cannot have duplicate user_Ids for the same group_Id, eg this could never happens:

group     user
   1         3
   1         3

Edit: You can build your query in the following way:

<?php
$ids = array(3, 7, 100);
$power = 2;
$query = "
    SELECT
    group_Id,
    SUM(
        IF user_Id = " .$ids[0]. " THEN 1 ";
foreach ($id in $ids) { 
    $query .= " ELSEIF user_Id = $id THEN " . $power;
    $power = $power * 2;
}
$query .= " ELSE $power
    ) AS bits
    FROM myGroupTable
    GROUP BY group_Id
    HAVING bits = " . ($power - 1);

Upvotes: 2

Related Questions