Help with sql query

Discussion in 'Databases' started by sc_king, Dec 14, 2009.

0
  1. #1
    Hi,

    I need to grab the total number of videos for each category where date_added is < today. Each video can be in multiple categories.

    Video
    id
    title
    description
    date_added

    Video_Categories
    id
    video_id
    category_id

    What's the best way to join these tables to get what i'm looking for?

    Thanks
     
    sc_king, Dec 14, 2009 IP
  2. cahya77

    cahya77 Member

    Messages:
    80
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #2
    hi...
    from your tables structure...
    i guess that Video.id is related to Video_Categories.id...
    if that's true may be you can try this query

    select vc.category_id, count(*) as tot_number
    from Video_Categories vc, Video v
    where v.id = vc.id and date_added < 'yyyy-mm-dd'
    group by category_id

    thank's....
     
    cahya77, Dec 14, 2009 IP
  3. shiva9675

    shiva9675 Peon

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    hmm.. not sure if you really need to join the videos table.. just adds to the turnaround time. you could simply try

    select categories_id, count(video_id) as vid_count
    from video_categories
    group by categories_id

    I think that will do the trick.
    Cheers.
     
    shiva9675, Dec 14, 2009 IP
  4. sc_king

    sc_king Peon

    Messages:
    29
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thanks for the reply guys,

    Cahya77, that worked. Did some testing on a lot of entries and it seemed to be pretty fast.

    I needed to join the tables since I need to account for the date.
     
    sc_king, Dec 15, 2009 IP