Use custom column in the where clause from multiple joined tables

Question

I am creating a custom column with an alias searchColumn, which which should be used in the where clause. How is this possible?

  SELECT lgs.*,
       lg.*,
       Concat(Ifnull(lgs.beneficiaryfirstname, ''),
       Ifnull(lgs.beneficiarylastname, ''),
       Ifnull(lg.Name, '')) AS
       "searchColumn"
FROM   local_giveaways_submissions lgs
       INNER JOIN local_giveaways lg
               ON lg.giveawayid = lgs.giveawayid
       LEFT JOIN local_giveaways_partners lgp
              ON lgs.partnerid = lgp.partnerid
WHERE  "searchcolumn" LIKE '%asd%'

Update: Do you guys think it's better to use concat_ws() for search?

CONCAT_WS(
    '%',
    CONCAT(lgs.FirstName,' ',lgs.LastName),
    lgs.EmailAddress,
    CONCAT(lgs.BeneficiaryFirstName,' ',lgs.BeneficiaryLastName),
    lg.Name,
    lgp.PartnerName
) AS "SearchColumn"

In MySQL you cannot refer to output expression alias in WHERE clause, But you may do this in HAVING clause (and GROUP BY not needed - MySQL extension). Alternatively you may use the expression instead of alias. But in this particular case you may use `WHERE lgs.beneficiarylastname LIKE '%asd%'` - both NULL and `''` does not match. — Akina, May 18 '21 at 05:06
Sorry didn't want to put duplicate column there. Updated my code — csandreas1, May 18 '21 at 05:11
Does this answer your question? [Using column alias in WHERE clause of MySQL query produces an error](https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — AcidResin, May 18 '21 at 05:12
And again - neither COALESCE nor IFNULL affects LIKE testing result. These functions are useful for data output but excess for data processing. While using in WHERE/HAVING (directly or indirectly) they "kills" possible index usage and degrades the performance. — Akina, May 18 '21 at 05:24
@Akina see updated question. Do you suggest using concat_ws() ? Performance doesn't matter much here because the data is not so much — csandreas1, May 18 '21 at 07:36

score 3 · Accepted Answer · answered May 18 '21 at 05:07

You cannot refer to the alias searchColumn in the WHERE clause at the same level of the query, as this alias is not yet available. However, MySQL has overloaded the HAVING operator for this purpose:

SELECT lgs.*, lg.*,
   CONCAT(IFNULL(lgs.beneficiaryfirstname, ''),
   IFNULL(lgs.beneficiarylastname, ''),
   IFNULL(lgs.beneficiarylastname, '')) AS searchColumn
FROM local_giveaways_submissions lgs
INNER JOIN local_giveaways lg
    ON lg.giveawayid = lgs.giveawayid
LEFT JOIN local_giveaways_partners lgp
    ON lgs.partnerid = lgp.partnerid
HAVING
    searchcolumn LIKE '%asd%'     -- this is allowed in MySQL

Use custom column in the where clause from multiple joined tables

1 Answers1

Linked