Jeremy Talanay Jeremy Talanay - 10 days ago 6
MySQL Question

MySql Reuse Alias for WHERE clause

I have this query that check every keyword entered from search textbox and must return with most matched keywords.
I have made the query and its working fine. 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


is there any way to do it? Thanks in advanced and sorry for my bad english :)

Answer

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