CODEWITHSUNDEEP
phpmysqlsql
user994461
user994461

Reputation: 530

Count the number of rows and back result if is greater than 0

I have try to display result only if content > 0

SELECT actors.*, (SELECT count(*) FROM `content_actors`
                  WHERE content_actors.actor = actors.record_id) AS getCount
FROM actors

If i try to add in query 

WHERE getCount > 0

I will have error

Unknown column 'getCount' in 'where clause'

Upvotes: 1

Views: 85

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

In MySQL, you can use a having clause:

SELECT actors.*,
       (SELECT count(*) FROM `content_actors` WHERE content_actors.actor = actors.record_id) AS getCount
FROM actors
HAVING getCount > 0;

This is a MySQL extension.

Assuming actors.record_id is unique (presumably a primary key), this could be written as:

SELECT a.*, COUNT(*) as getCount
FROM actors a JOIN
     content_actors ca
     ON ca.actor = a.record_id
GROUP BY a.record_id;  -- assumes this is unique/primary key

No filtering is needed, because the JOIN requires at least one match.

Upvotes: 3

Related Questions