CODEWITHSUNDEEP
mysql
gobang
gobang

Reputation: 3

Error: Subquery returns more than 1 row, when using IN

I have a problem why the query is an error when I use IN inside the IF

SET @level  := '2';
SET @id_user    := '2';

SELECT *
FROM pg_akun
WHERE 
    level_akun = @level
    AND parent_id_akun IN (
        IF(@level IN ('1', '2'), (SELECT DISTINCT(parent_id_akun) FROM pg_akun WHERE level_akun = @level), @id_user)
    )
    AND id_akun NOT IN (SELECT id_akun FROM pg_trans WHERE id_user = '1' AND pengajuan = '1')

Report error

Query: select * from pg_akun where level_akun = @level AND parent_id_akun in ( if(@level in ('1', '2'), (SELECT DISTINCT(parent_id_akun...

Error Code: 1242.
Subquery returns more than 1 row

Upvotes: 0

Views: 24

Answers (1)

Barmar
Barmar

Reputation: 780974

IF has to return a single value, it can't return a derived table that can be used in IN.

Move the IF outside IN.

SELECT *
FROM pg_akun
WHERE 
    level_akun = @level
    AND IF(@level IN ('1', '2'),
            parent_id_akun IN (SELECT DISTINCT parent_id_akun FROM pg_akun WHERE level_akun = @level),
            parent_id_akun = @id_user)
    AND id_akun NOT IN (SELECT id_akun FROM pg_trans WHERE id_user = '1' AND pengajuan = '1')

BTW, DISTINCT is not a function that applies to a single column, it's a keyword that applies to the entire SELECT list. You don't need parentheses around the column name.

Upvotes: 1

Related Questions