The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

that have videos which belong to sub categories of a certain main category.

The links / joins are:

a channel can have many videos. (channel table AS ch)

each video can belong to 1 sub category (video table AS v)

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 '&#37;|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:

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.

This is a clear sign that this table hasn't been normalized. If possible, you should change your Db structure: eliminate the 'Niche' field in the category table, and instead make a 'Niche' table with two fields (subcategory id and category id).

[*]a channel can have many videos. (channel table AS ch)[*]each video can belong to 1 sub category (video table AS v)[*]1 sub category can belong to numerous main categories. (category table AS c).

They can, but do they belong to at least 1 video, sub category and main category? Or is it possible that a channel has no videos at all, etc. And do you want to select those channels too?

Hmmmm, is it possible for these queries to ONLY select those videos within a channel that belong to the filter category.

For example a channel may have 6 videos in it, 2 of which belong to 1 niche (say 1) and 4 of which belong to another niche.

If a channel has mixed videos from different categiries it should only show those from the "selected filter" category.

However if a channel has ONLY videos from 1 category, the desired or not desired category these sql statements work perfectly. If it has no videos from desired category the channel will be ignored. Which is great. But if it has mixed, it should only display the desired videos from the "mix". If that makes sense.