Mysql: WHERE statement that depends of the COUNT() of a column?

I've read loads of answers on SO, but still can't get this to work. I'm pretty sure I need a subquery but don't know where I should begin...

Here's readable/psuedo of what i want to do:

SELECT DISTINCT cat_sub.*, COUNT(job.job_id) AS num_properties_which_meet_criteria
FROM cat_sub
LEFT JOIN job_cat USING (sc_id)
LEFT JOIN job USING (job_id)
WHERE job.village_id=2 AND num_properties_which_meet_criteria > 0
GROUP BY sc_id 

I'm trying to make my WHERE reference the amount of valid results in the job table, which in this instance are those which matchjob.village_id=2.

So just to make clear, i don't want the total num开发者_JS百科ber of rows matched by the join. I want to total num rows matched by the join and meeting the WHERE criteria.

Really appreciate any help!

You test the value of an aggregate function with a HAVING clause.

SELECT DISTINCT cat_sub.*, COUNT(job.job_id) AS num_properties_which_meet_criteria
FROM cat_sub
LEFT JOIN job_cat USING (sc_id)
LEFT JOIN job USING (job_id)
WHERE job.village_id=2 
GROUP BY sc_id
HAVING COUNT(job.job_id) > 0

Use having instead of where:

WHERE job.village_id=2
GROUP BY sc_id 
HAVING num_properties_which_meet_criteria > 0

You need to have a "HAVING" clause, there you can use conditions on aggregate functions used in the SELECT part of the query.

Did you try:

SELECT DISTINCT cat_sub.*, COUNT(job.job_id)
FROM cat_sub
LEFT JOIN job_cat USING (sc_id)
LEFT JOIN job USING (job_id)
WHERE job.village_id=2 
GROUP BY sc_id 
HAVING COUNT(job.job_id) > 0

UPDATE: Removed alias