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 "%example@example.com%"
OR user_extra.fname like "%test%"
OR tp.login like "%461988%"
Advertisement
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 '%example@example.com%'
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 '%sradesign.net@gmail.com%'
OR fname like '%test%'
OR login like '%461988%'