Here is my subQuery, I cannot understand the error : 1242 - Subquery returns more than 1 row

SELECT DISTINCT u.id as userId,  
  (
                SELECT a10.description 
                FROM Export exp 
                INNER JOIN Account a10 ON (a10.id = exp.accountId)
                WHERE exp.isActive = 1 
                AND exp.platformId = 14
            ) as structur
            FROM User u
            LEFT JOIN AccountUser au ON au.userId = u.id and au.isDefault = 1
            LEFT JOIN Account a ON a.id = au.accountId
            WHERE u.id > 0
            ORDER BY u.id ASC
            LIMIT 10

ERROR : 1242 - Subquery returns more than 1 row

SELECT DISTINCT u.id as userId,  
  (
                SELECT a10.description 
                FROM Export exp

Thank you for your help

CodePudding user response：

Your subquery return more than a row but a row cell accept just one value so if you really need this subquery then you should limit the result

SELECT DISTINCT u.id as userId,  
  (
                SELECT a10.description 
                FROM Export exp 
                INNER JOIN Account a10 ON (a10.id = exp.accountId)
                WHERE exp.isActive = 1 
                AND exp.platformId = 14
                LIMIT 1
            ) as structur
            FROM User u
            LEFT JOIN AccountUser au ON au.userId = u.id and au.isDefault = 1
            LEFT JOIN Account a ON a.id = au.accountId
            WHERE u.id > 0
            ORDER BY u.id ASC
            LIMIT 10

but looking to your code you could also avoid the subquery adding the join at the main query

SELECT DISTINCT u.id as userId,  Account.description structur
    FROM User u
    LEFT JOIN AccountUser au ON au.userId = u.id and au.isDefault = 1
    LEFT JOIN Account a ON a.id = au.accountId
    LEFT JOIN exp ON ON Account.id = exp.accountId
        AND exp.isActive = 1 
        AND exp.platformId = 14
    WHERE u.id > 0
    ORDER BY u.id ASC
    LIMIT 10