SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Evangelist
    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' 
    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 

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by LuckyB View Post
    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?

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Sorry, never mind. Let's see if I can put that query together

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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|%'

  6. #6
    SitePoint Evangelist
    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).

  7. #7
    SitePoint Evangelist
    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.

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by LuckyB View Post
    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:
    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_to_filter AS c2f
    ON v.CATID = c2f.CATID
    WHERE ch.active = '1' 
    AND   c2f.NICHE = 0

  9. #9
    SitePoint Evangelist
    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 
    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?
    Last edited by LuckyB; Dec 15, 2008 at 04:56. Reason: Corrected SQL but still aint working

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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.

  11. #11
    SitePoint Evangelist
    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' 
    The CONCAT('0|',ch.CHID,'|0') is required because the video table isn't normalized.

    Now I've got to get the following working:
    PHP Code:
    SELECT ch.CHIDch.namech.seo_name from channel ch 
    where ch
    .active 
    ORDER BY ch
    .sort_order ASCch.name ASC 
    LIMIT 10 

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    This
    Code:
    WHERE ch.active = '1' and ( c2f.FID = '1' or c2f.FID = '2' )
    Can be written like
    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.
    Then you'd better normalize it

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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

  14. #14
    SitePoint Evangelist
    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.CHIDch.namech.seo_nameCOUNT(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 and ( c2f.FID '1' )
    ORDER BY channel_count DESCch.name ASC limit 012 
    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.

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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

  16. #16
    SitePoint Evangelist
    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.

  17. #17
    SitePoint Evangelist
    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.

  18. #18
    SitePoint Evangelist
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •