Can't use where clause on correlated columns.

Question

I want to add a where clause to make sure video_count is greater than zero. Only categories which are referenced once or more in video_category.video_id should be returned.

Because video_count is not a field in any table I cannot do this.

Here is the query.

SELECT category . * , (
        SELECT COUNT( * )
        FROM video_category
        WHERE video_category.category_id = category.category_id
        ) AS 'video_count'
        FROM category
        WHERE category.status = 1
    AND video_count > '0'
        AND publish_date < NOW()
        ORDER BY updated DESC;

Thanks for the help.

Answer 1

SELECT c.title,COUNT(vc.category_id) 
  FROM categories c
     , video_categories vc 
 WHERE c.id=vc.category_id 
   AND c.status=1 
   AND c.publish_date < NOW() 
 GROUP BY c.id;

Answer 2

Rewrite with a sub-query:

SELECT *
FROM (
    SELECT category . * , (
        SELECT COUNT( * )
        FROM video_category
        WHERE video_category.category_id = category.category_id
        ) AS 'video_count'
        FROM category
        WHERE category.status = 1
        AND publish_date < NOW()
    ) dummy
WHERE video_count > '0'
    ORDER BY updated DESC;

Answer 3

I don't know if this is the best answer, but it worked for me.

SELECT category. * , (

SELECT COUNT( * )
FROM video_category
WHERE video_category.category_id = category.category_id
) AS 'video_count'
FROM category
WHERE category.status =1
AND (

SELECT COUNT( * )
FROM video_category
WHERE video_category.category_id = category.category_id
) >0
AND publish_date < NOW( )
ORDER BY updated DESC

Answer 4

I think you can achive it using GROUP BY & HAVING clause.

SELECT category . *
     , COUNT(v.*)  as video_count
  FROM category c
     , video_category v
 WHERE v.category_id = c.category_id
   AND c.status = 1
   AND publish_date < NOW()
 GROUP BY v.category_id 
HAVING COUNT(v.*) > 0
ORDER BY updated DESC;

NO check though

Answer 5

select category.*, count(video_category.category_id) as c
from category inner join video_category using(category_id)
where category.status = 1
and publish_date < now()
group by video_category.category_id
having c > 0
order by updated desc

this should do the trick

Answer 6

This would be the ideal way to do it:

SELECT c.id, COUNT(vc.id)
  FROM category as c
  LEFT JOIN video_category as vc
  on c.categoryid = vc.categoryid
 WHERE c.status = 1
   AND vc.categoryid is not null
   AND c.publish_date < NOW()
 GROUP BY c.id;

Also, make sure you REALLY need to select c.*

selecting * can hurt performance.