axon axon - 4 months ago 9
SQL Question

How to recognize parent alias in Join (SELECT?

I get the error below:


ER_BAD_FIELD_ERROR: Unknown column 'c.id' in 'where clause':


SELECT *
FROM clients c
LEFT JOIN
(SELECT GROUP_CONCAT(smpp_user), client_id
FROM client_accounts
WHERE client_id = c.id) AS l ON
l.client_id = c.id


I need use WHERE to group
smpp_user
columns for each
c.id
from main SELECT.

Help please? I believe it's possible.

Answer

Just remove WHERE clause in your sub query and use GROUP BY:

SELECT *
FROM clients c
LEFT JOIN  (
    SELECT GROUP_CONCAT(smpp_user), client_id
    FROM client_accounts
    GROUP BY client_id
) AS l ON l.client_id = c.id