Mysql help required

Discussion in 'OT Technology' started by o2, Jul 15, 2008.

  1. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    Im trying to select a list of categories, and display them, as well as list number of videos in each category.

    I have 2 tables, categories (cat_id, cat_name), and videos (vid_id, vid_catid, etc). cat_id = vid_catid.

    I need 1 query that will select the category names + number of rows in the video table that has each category in its vid_catid column.

    How would I accomplish that?
     
  2. TheDarkHorizon

    TheDarkHorizon \xC0\xFF\xEE

    Joined:
    Sep 26, 2002
    Messages:
    2,396
    Likes Received:
    0
    Location:
    San Francisco, CA
    Code:
    SELECT c.cat_id, c.cat_name, count(v.vid_id) FROM categories c, videos v WHERE c.cat_id=v.vid_catid GROUP BY c.cat_id, c.cat_name;
     
  3. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    Thanks, but how do I display the actual count?

    Also, this omits the empty categories, and doesn't output them.
     
  4. durondude

    durondude OT Addict

    Joined:
    May 7, 2001
    Messages:
    24,062
    Likes Received:
    5
    Location:
    Earth, The Planet
    throw a left join in there
     
  5. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    I know how to use left joins, I just dont know how to count the number of items in the videos table that match each catid.
     
  6. jdw

    jdw New Member

    Joined:
    Dec 31, 2005
    Messages:
    4,429
    Likes Received:
    0
    Location:
    ND

    count(v.vid_id) that should be doing it...
     
  7. ge0

    ge0 New Member

    Joined:
    Oct 31, 2005
    Messages:
    8,398
    Likes Received:
    0
    Location:
    JERSEY
    count(v.vid_id) As vid_id_count then use that to call it ont he page
     
  8. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    Great that worked!

    But how do I still get it to show the categories that have 0 items in them?
     
  9. durondude

    durondude OT Addict

    Joined:
    May 7, 2001
    Messages:
    24,062
    Likes Received:
    5
    Location:
    Earth, The Planet
    Lets see your query
     
  10. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    Code:
    SELECT c.cat_id, c.cat_name, count(v.vid_id) As vid_id_count FROM categories c, videos v WHERE c.cat_id=v.vid_catid GROUP BY c.cat_id, c.cat_name ORDER BY cat_name
     
  11. durondude

    durondude OT Addict

    Joined:
    May 7, 2001
    Messages:
    24,062
    Likes Received:
    5
    Location:
    Earth, The Planet
    Where is the left join?
     
  12. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    Its just a simple join.
     
  13. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    His "join" is in the WHERE (which is identical to INNER)
     
  14. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    If you want to list categories with no vids, you have to use a left or right join, not an inner join or a table,table where a=b

    Code:
    SELECT c.cat_id, c.cat_name, count(v.vid_id) As vid_id_count FROM categories c LEFT JOIN  videos v ON c.cat_id=v.vid_catid GROUP BY c.cat_id, c.cat_name ORDER BY cat_name
    
    Keep catgories on the left and videos on the right. This may omit videos that have none or invalid category though
     
  15. o2

    o2 Witty Title Here OT Supporter

    Joined:
    Oct 4, 2005
    Messages:
    16,099
    Likes Received:
    12
    Location:
    Toronto
    Ohh man, you are right, I cant believe I forgot about that.

    Thanks, that works perfectly.
     

Share This Page