Hi guys

I've got a few queries that are currently working, but not to be extended, and I think the way to do it is with joins.

PHP Code:
SELECT count(ch.CHID) AS total_count FROM channel ch WHERE ch.active '1' 
I need to change this so it only counts channels:
  • that have videos which belong to sub categories of a certain main category.

The links / joins are:
  1. a channel can have many videos. (channel table AS ch)
  2. each video can belong to 1 sub category (video table AS v)
  3. 1 sub category can belong to numerous main categories. (category table AS c). The categories table stores the main categories of a subcategory in 1 field called "Niche" which holds the data like |0|2|. So I use " from category c where c.niche LIKE '%|0|%' " to select sub categories that belong to main category 0.

Hope that makes some sense. One other query that need to be extended like above i s:
PHP Code:
SELECT from channel ch where ch.active 1 ORDER BY ch.sort_order ASCch.name ASC LIMIT 10