MYSQL: how to return value from column that has relation to values in another column

Question

Im struggling to articulate this, let alone execute in in MYSQL. How Do I return the userId X where userId.X and permissionId in (1,2,3,4,5,6,7,8) ?

The below example should return 6.

MariaDB [mailserver]> select * from user2permission;
+--------------+--------+
| permissionId | userId |
+--------------+--------+ 
|            1 |      5 |
|            1 |      6 |
|            2 |      6 |
|            2 |      7 |
|            3 |      6 |
|            4 |      6 |
|            5 |      6 |
|            6 |      6 |
|            7 |      6 |
|            8 |      6 |
+--------------+--------+

`select userId from user2permission where permissionId in (1,2,3,4,5,6,7,8);` This should work. — Nivethi Mathan, May 25 '18 at 07:38
Possible duplicate of [Matching all values in IN clause](https://stackoverflow.com/questions/11636061/matching-all-values-in-in-clause) — Gaurav, May 25 '18 at 07:40
I believe the standard docs for **ALL** can help you - https://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html — Sudipta Mondal, May 25 '18 at 07:42
@Sudipta Mondal: No, `ALL` doesn't help with this kind of problem. — Thorsten Kettner, May 25 '18 at 08:40

score 3 · Accepted Answer · answered May 25 '18 at 07:39

3

This kind of query can be written with the use of Group By clause and counting the instance as per the filteration applied in where clause

SELECT userId
FROM user2permission 
WHERE permissionId  IN (1,2,3,4,5,6,7,8)
GROUP BY userId 
HAVING COUNT(*) = 8

answered May 25 '18 at 07:39

Rizwan

2,369
22
27

Thank you Rizwan. This is the clarification i needed. – dom May 25 '18 at 08:00
1

@dom Note that the implication here (not unreasonably) is that (userid,permissionid) is unique – Strawberry May 25 '18 at 08:18

score 0 · Answer 2 · answered May 25 '18 at 07:38

You want to only show users that have an entry for all required permissions. Two solutions spring to mind:

select *
from users
where userid in (select userid from user2permission where permissionid = 1)
  and userid in (select userid from user2permission where permissionid = 2)
  ...

and

select userid,
from user2permission
group by userid
having count(case when permissionid = 1 then 1 end) > 0
   and count(case when permissionid = 2 then 1 end) > 0
   ...

And when I say "spring" to mind, I mean exactly that = without much thinking :-) Rizwan's aggregation solution is what you should use.

Yeah, less springing maybe :-$ – Strawberry May 25 '18 at 08:19 — Strawberry, May 25 '18 at 08:19

MYSQL: how to return value from column that has relation to values in another column

2 Answers2