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 ?

Answer 1

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) )

Answer 2

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)