mysqlsemi-join

MySQL subquery IN multiple values


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 ?


Solution

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