Join query? help

[LuckyB]

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.

Eg:

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:

SELECT * from channel ch where ch.active = 1 ORDER BY ch.sort_order ASC, ch.name ASC LIMIT 10 

[guido2004]

I think the way to do it is with joins.

Yes it is

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?

[LuckyB]

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?

It is possible that they have no videos. But hopefully that is rare.

[guido2004]

Sorry, never mind. Let's see if I can put that query together

[guido2004]

I assumed that a video can belong to only one channel, and that the channel id is a field in the video table.

SELECT 
  COUNT(DISTINCT ch.CHID) AS total_count 
FROM channel ch 
INNER JOIN video AS v
ON ch.CHID = v.CHID
INNER JOIN category AS c
ON v.CATID = c.CATID
WHERE ch.active = '1' 
AND   c.NICHE LIKE '%|0|%'

[LuckyB]

Thanks.. Im trying to normalize it as you suggested.
With a category_to_filter c2f table (filter being niche).

[LuckyB]

Yes, a video can only belong to 1 channel.

I've got the channel_to_filter table setup and I have dropped the niche field.

[guido2004]

Yes, a video can only belong to 1 channel.

I've got the channel_to_filter table setup and I have dropped the niche field.

Great. Then the query would become:

SELECT 
  COUNT(DISTINCT ch.CHID) AS total_count 
FROM channel ch 
INNER JOIN video AS v
ON ch.CHID = v.CHID
INNER JOIN category_to_filter AS c2f
ON v.CATID = c2f.CATID
WHERE ch.active = '1' 
AND   c2f.NICHE = 0

[LuckyB]

Thanks.

This is what I have so far:

SELECT count(DISTINCT ch.CHID) AS total_count 
FROM channel ch 
INNER JOIN video AS v
ON v.channel = CONCAT('0|',ch.CHID,'|0')
INNER JOIN category AS c 
ON v.category = CONCAT('0|',c.CID,'|0')
INNER JOIN category_to_filter AS c2f 
ON c2f.CID = c.CID 
WHERE ch.active = '1' 
and ( c2f.FID = '2' ) 

And it isn't working. Getting 0 results. Where if I do it mentally, I know there should be 1 result. Is there anything "wrong" with this query?

[guido2004]

What is this: CONCAT('0|',ch.CHID,'|0') ?

And there is no need to pass through the category table, if you don't need any data from it. You can link the video table directly to the c2f table.

[LuckyB]

Ok, this works, thanks so much for your help:

SELECT count(DISTINCT ch.CHID) AS total_count
FROM channel ch
INNER JOIN video AS v ON v.channel = CONCAT('0|',ch.CHID,'|0')
INNER JOIN category_to_filter AS c2f ON v.category = CONCAT( '0|', c2f.CID, '|0' )
WHERE ch.active = '1' and ( c2f.FID = '1' or c2f.FID = '2' ) 

The CONCAT('0|',ch.CHID,'|0') is required because the video table isn't normalized.

Now I've got to get the following working:

SELECT ch.CHID, ch.name, ch.seo_name from channel ch 
where ch.active = 1 
ORDER BY ch.sort_order ASC, ch.name ASC 
LIMIT 10 

[guido2004]

This

WHERE ch.active = '1' and ( c2f.FID = '1' or c2f.FID = '2' )

Can be written like

WHERE ch.active = '1' AND c2f.FID IN ('1', '2')

The CONCAT('0|',ch.CHID,'|0') is required because the video table isn't normalized.

Then you'd better normalize it

[guido2004]

SELECT DISTINCT ch.CHID, ch.name, ch.seo_name 
FROM channel ch
INNER JOIN video AS v 
ON v.channel = CONCAT('0|',ch.CHID,'|0')
INNER JOIN category_to_filter AS c2f 
ON v.category = CONCAT( '0|', c2f.CID, '|0' )
WHERE ch.active = '1' 
AND c2f.FID IN ('1', '2')
ORDER BY ch.sort_order ASC, ch.name ASC 
LIMIT 10

[LuckyB]

Thanks thats nice short hand.

I got that last query working. But struggling with another:

SELECT distinct ch.CHID, ch.name, ch.seo_name, COUNT(v.VID) AS channel_count
from channel ch
INNER JOIN video AS v ON v.channel = CONCAT('0|',ch.CHID,'|0') 
INNER JOIN category_to_filter AS c2f ON v.category = CONCAT( '0|', c2f.CID, '|0' )
where ch.active = 1 and ( c2f.FID = '1' )
ORDER BY channel_count DESC, ch.name ASC limit 0, 12 

It technically works but returns the wrong results. channel_count is in this query as it is meant to be ordered by the quantity of videos in the channel.

[guido2004]

The GROUP BY is missing:

SELECT distinct ch.CHID, ch.name, ch.seo_name, COUNT(v.VID) AS channel_count
from channel ch
INNER JOIN video AS v 
ON v.channel = CONCAT('0|',ch.CHID,'|0') 
INNER JOIN category_to_filter AS c2f 
ON v.category = CONCAT( '0|', c2f.CID, '|0' )
where ch.active = 1 
and c2f.FID = '1'
GROUP BY ch.CHID, ch.name, ch.seo_name
ORDER BY channel_count DESC, ch.name ASC limit 0, 12

[LuckyB]

Thanks, such a SIMPLE mistake!
I missed that. I had that line in my existing query but missed it.

[LuckyB]

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.

[LuckyB]

Sorry, my mistake, it does this.

And I found another mistake, I was doing:
WHERE ch.active = '1' AND c2f.FID IN ('1' or '2')
BUT I should have done:
WHERE ch.active = '1' AND c2f.FID IN ('1', '2')