Problem with Concat results searching in MYSQL

Hi this is the query I’m trying to achieve results with but how can I get the search for concat results in that column?

SELECT 
     ( SELECT GROUP_CONCAT(tp.login) 
         FROM tp 
        WHERE tp.user_id = user_extra.user_id
     ) as login
     , users.email as email
     , user_extra.fname as name 
  FROM user_extra 
  LEFT 
  JOIN users 
    ON users.id = user_extra.user_id 
 WHERE users.email like "[e-mail]%" 
    OR user_extra.fname like "%test%" 
    OR tp.login like "%461988%"

Answer

You can’t refer to an alias defined in the select clause in the same scope (left apart the order by clause). In very recent versions of MySQL (8.0.14 or higher), you can use lateral for this:

SELECT tp.login, u.email as email, ue.fname as name 
FROM user_extra ue
LEFT JOIN users u ON u.id = ue.user_id 
LEFT JOIN LATERAL (
    SELECT GROUP_CONCAT(tp.login) login
    FROM tp 
    WHERE tp.user_id = ue.user_id
) tp ON 1
WHERE 
    u.email like '[e-mail]%'
    OR ue.fname like '%test%'
    OR tp.login like '%461988%'

In earlier versions, you can JOIN, or use a subquery:

SELECT *
FROM (
    SELECT 
        (SELECT GROUP_CONCAT(tp.login) login FROM tp WHERE tp.user_id = ue.user_id) login, 
        u.email as email, 
        ue.fname as name 
    FROM user_extra ue
    LEFT JOIN users u ON u.id = ue.user_id 
) t
WHERE 
    email like '[e-mail]%'
    OR fname like '%test%'
    OR login like '%461988%'