CODEWITHSUNDEEP
mysqlselect
Василий Васильевич
Василий Васильевич

Reputation: 63

How to select one record from table with multiple condition at same field?

I have a table like this:

---------------------
| id | user | group |
---------------------
|  0 |  103 |    27 |
|  1 |  107 |    31 |
|  2 |  103 |    32 |
|  3 |  112 |    31 |
|  4 |  135 |    64 |
|  5 |  135 |    66 |
---------------------

This is table of groups and users and as you can see there can be records with same user and different groups. How can I select, for example, the records with same user and two groups - 27 and 32? It would be the same user.

So, how can I do this?

EDIT: To show expected result I wrote a sample:

SELECT user FROM table ... (maybe WHERE group = 27 AND group = 32)

-- gives user id 103

EDIT 2: I've found it!

Here is query that perfectly working for me:

SELECT * FROM table
WHERE `user` in
(
    SELECT `user` FROM table
    WHERE `group` = <needed_group_1>
)
AND `group` = <needed_group_2>

Thank's to all for help, I've found all your answers useful!

Upvotes: 4

Views: 3038

Answers (3)

juergen d
juergen d

Reputation: 204924

To get all data that has more than 1 group do

select * from your_table
where `user` in
(
  select `user`
  from your_table
  group by `user`
  having count(distinct `group`) >= 2
)

To get the data that has 2 specific groups do

select * from your_table
where `user` in
(
  select `user`
  from your_table
  group by `user`
  having sum(`group` in (27,32)) >= 2
)

Upvotes: 2

echo_Me
echo_Me

Reputation: 37243

you almost got it

try this

  SELECT user FROM table1 WHERE id in ( select `group` = 27 AND `group` = 32
                                      from table1)
  group by user

NOTE. : group is reserved mysql keyword so escape it by backticks

this will give :

 USER
 103

DEMO HERE

Upvotes: 1

RandomSeed
RandomSeed

Reputation: 29809

SELECT user
FROM theTable
GROUP BY user
HAVING COUNT(group) = 2

This might also help you:

SELECT
    user,
    COUNT(*) as cnt,
    GROUP_CONCAT(group)
FROM theTable
GROUP BY user

And if you want to grab all records that match your condition:

SELECT theTable.*
FROM theTable
JOIN (
    SELECT user
    FROM theTable
    GROUP BY user
    HAVING COUNT(group) = 2
) AS UsersWithTwoGroups USING(user)

Upvotes: 1

Related Questions