MySql reuse alias for WHERE clause

Question

I have this query that checks every keyword entered from search textbox and must return with most matched keywords.

The problem is, I would like to exclude all the KW_MATCHED with 0 values in the returned row.

SELECT
    A1.*, (
        SELECT
            sum(
                CASE
                WHEN (A1.ID = A2.tag_id)
                AND (
                    A2.keyword = 'keyword1' || A2.keyword = 'keyword2'
                ) THEN
                    1
                ELSE
                    0
                END
            )
        FROM
            tbl_article_tags A2
    ) AS KW_MATCHED
FROM
    tbl_article A1
ORDER BY
    KW_MATCHED DESC

Result:

+----+---------------+----------------+------------+
| ID | Title         | Content        | KW_MATCHED |
+----+---------------+----------------+------------+
| 1  | title         | Lorem Ipsum... | 7          |
+----+---------------+----------------+------------+
| 2  | another title | Lorem Ipsum... | 5          |
+----+---------------+----------------+------------+
| 3  | another title | Lorem Ipsum... | 0          |
+----+---------------+----------------+------------+
| 4  | another title | Lorem Ipsum... | 0          |
+----+---------------+----------------+------------+

I tried re-using the alias KW_MATCHED for the WHERE clause (see my query below) but it returns this message:

[Err] 1054 - Unknown column 'KW_MATCHED' in 'where clause'

SELECT
    A1.*, (
        SELECT
            sum(
                CASE
                WHEN (A1.ID = A2.tag_id)
                AND (
                    A2.keyword = 'keyword1' || A2.keyword = 'keyword2'
                ) THEN
                    1
                ELSE
                    0
                END
            )
        FROM
            tbl_article_tags A2
    ) AS KW_MATCHED
FROM
    tbl_article A1
WHERE
    KW_MATCHED > 0
ORDER BY
    KW_MATCHED DESC

How can I do it correctly?

PaulF · Accepted Answer · 2016-11-30T09:08:36.993

0

Try using HAVING rather than WHERE

SELECT
A1.*, 
(SELECT sum(case when (A1.ID=A2.tag_id) AND (A2.keyword='keyword1' || A2.keyword='keyword2') then 1 else 0 end) FROM tbl_article_tags A2) as KW_MATCHED
FROM tbl_article A1
HAVING KW_MATCHED > 0
ORDER BY KW_MATCHED DESC

edited Nov 30 '16 at 09:08

answered Nov 29 '16 at 15:52

PaulF

6,673
2
18
29

Note there are some slight differences in how WHERE & HAVING work, but I don't think this will be an issue for where the alias is for a subquery column. See : http://stackoverflow.com/questions/2905292/where-vs-having/18710763#18710763 – PaulF Nov 29 '16 at 15:55
oh yes. my concept for this query is that calculate the scores of matched keyword THEN, filter it using `HAVING KW_MATCHED > 0` Do you mean with this type of query it would lower the performance? is that why WHERE is efficient to use than HAVING? – Jeremy Talanay Nov 29 '16 at 16:05
I often wonder why people downvote accepted answers without giving an explanation why - if my answer is not the best then surely Jeremy deserves to know the better way of solving his problem. – PaulF Nov 30 '16 at 09:11

MySql reuse alias for WHERE clause

1 Answers1