SitePoint Sponsor |
|
User Tag List
Results 1 to 18 of 18
Thread: Join query? help
-
Dec 15, 2008, 03:18 #1
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Join query? help
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:
PHP Code:SELECT count(ch.CHID) AS total_count FROM channel ch WHERE ch.active = '1'
- 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 '%|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 ASC, ch.name ASC LIMIT 10
-
Dec 15, 2008, 03:30 #2
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.
[*]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).Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Dec 15, 2008, 03:48 #3
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Dec 15, 2008, 03:58 #4
Sorry, never mind. Let's see if I can put that query together
Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Dec 15, 2008, 04:06 #5
I assumed that a video can belong to only one channel, and that the channel id is a field in the video table.
Code: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|%'
Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Dec 15, 2008, 04:18 #6
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks.. Im trying to normalize it as you suggested.
With a category_to_filter c2f table (filter being niche).
-
Dec 15, 2008, 04:25 #7
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Dec 15, 2008, 04:29 #8Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Dec 15, 2008, 04:51 #9
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks.
This is what I have so far:
PHP Code: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' )
Last edited by LuckyB; Dec 15, 2008 at 04:56. Reason: Corrected SQL but still aint working
-
Dec 15, 2008, 05:04 #10
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.Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Dec 15, 2008, 05:15 #11
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Ok, this works, thanks so much for your help:
PHP Code: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' )
Now I've got to get the following working:
PHP Code: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
-
Dec 15, 2008, 05:24 #12
This
Code:WHERE ch.active = '1' and ( c2f.FID = '1' or c2f.FID = '2' )
Code: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.Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Dec 15, 2008, 05:26 #13Code:
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
Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Dec 15, 2008, 05:33 #14
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks thats nice short hand.
I got that last query working. But struggling with another:
PHP Code: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
-
Dec 15, 2008, 05:42 #15
The GROUP BY is missing:
Code: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
Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Dec 15, 2008, 05:51 #16
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks, such a SIMPLE mistake!
I missed that. I had that line in my existing query but missed it.
-
Dec 15, 2008, 06:01 #17
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Dec 15, 2008, 07:06 #18
- Join Date
- Jan 2005
- Posts
- 425
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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')
Bookmarks