CODEWITHSUNDEEP
mysql
Jeremy Talanay
Jeremy Talanay

Reputation: 33

MySql reuse alias for WHERE clause

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?

Upvotes: 0

Views: 553

Answers (1)

PaulF
PaulF

Reputation: 6783

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

Upvotes: 0

Related Questions