[RESOLVED] Subquery WHERE Problem

Subqueries seem to be the topic today on this thread ha.

This works:

SELECT t2.id, 
(SELECT COUNT( 'id' ) FROM table1 t1 WHERE creat_id = t2.id AND t1.published = "1") AS num FROM table2 t2

Adding in a WHERE

SELECT t2.id, 
(SELECT COUNT( 'id' ) FROM table1 t1 WHERE creat_id = t2.id AND t1.published = "1") AS num FROM table2 t2 WHERE num > 0

Says: Unknown column 'num' in 'where clause' .
Meanwhile num does show the correct values in the select without the WHERE.

First, why do you use a subquery?

Second, there is no column named num in table2.

Third, if you want to get result from a SQL function and not the content of the table use HAVING instead of WHERE.

SELECT t2.id, COUNT(t1.id)
FROM table2 t2 
LEFT JOIN table1 t1
	ON t1.creat_id = t2.id
WHERE t1.published = 1
HAVING COUNT(t1.id) > 0
GROUP BY t2.id

It is untested, but I think it will work.

Using the join duplicates the entry. Each count is one and matched to its row. So it shows something like

4 1
5 1
4 1

instead of

4 2
5 1

However using HAVING solved it, thanks.