MySQL subquery IN multiple values

Question

I have 2 tables :

Table reports

id   |  name
-------------
1    |  test 1
2    |  test 2
3    |  test 3

Table reports_access

id_table  |   group_id
-----------------------
1         |   1
1         |   2
1         |   3
1         |   4
2         |   1
2         |   2

I need to access reports depending on group_id of the logged user, a user belongs to multiple groups.

I tried :

SELECT reports.*
    FROM reports
WHERE (
    SELECT group_id 
        FROM reports_access AS repacc 
        WHERE repacc.id_table = reports.id
    ) IN (1, 3)

I got this error :

Subquery returns more than 1 row

I can't understand if I can do what I want using one request, because I need to test if an array belongs to other array.

Did I miss something ?

Does `(1, 3)` in the subquery represent a user id or a group_id? Either way I think you want a `join` + a `where` not a subquery. — Hunter McMillen, Mar 12 '18 at 20:22

score 2 · Accepted Answer · answered Mar 12 '18 at 20:23

2

I think you are looking for this:

 SELECT reports.*
        FROM reports
    WHERE id in (
        SELECT repacc.id_table 
            FROM reports_access   
           where group_id
        IN (1, 3) )

answered Mar 12 '18 at 20:23

Ole EH Dufour

2,968
4
23
48

This correctly implements a semi-join. The other answers (so far) result in duplicate rows. – Bill Karwin Mar 12 '18 at 20:25

stackFan · Answer 2 · 2018-03-12T20:41:58.777

0

How about using JOIN ?

SELECT DISTINCT r.*
FROM reports r 
INNER JOIN reports_access ra 
on ra.id_table=r.id 
where ra.group_id in (1,3)

edited Mar 12 '18 at 20:41

answered Mar 12 '18 at 20:23

stackFan

1,528
15
22

MySQL subquery IN multiple values

2 Answers2