Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    20

    Unanswered: Multiple Joins/Combining Information From 3 Tables

    I'm having trouble generating what I want from just one single query.

    I have 3 tables: uc_videos, uc_video_categories, and uc_list_of_parent_categories

    uc_videos has the fields video_id and author_id (those are the fields relevant to this query)
    uc_video_categories has the fields video_id and parent_category (parent_category is an ID #)
    uc_list_of_parent_categories has the fields id and category (category is text field, containing the text name of the parent category id)

    What I'm trying to generate is a list of categories, with the number of videos that a specific has uploaded for each category. If I can get a list of videos with their corresponding text-version category, I can generate the numbers with PHP after the sql.

    I'm so lost, not even sure where to start! I've read more on joins, and still cannot figure it out. Please help! Thanks!

    My current sql, which doesn't contain all the joins I think I need, as well as it doesn't even work, gives me an error:
    Code:
    SELECT a.video_id, category
    FROM uc_video_categories AS a, uc_list_of_video_parent_categories AS c, uc_videos as e
    INNER JOIN (SELECT video_id FROM uc_videos WHERE author_id = '16') AS f ON f.video_id = a.video_id

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please dump your tables, i.e. generate the CREATE TABLE statements, along with a few INSERT statements for each table, so that we can set up the tables and test the query ourselves
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    20
    Attached, hope I exported right, thanks!

    Edit: updated the uc_videos dump to contain a few entries
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your export was fine except the sample rows didn't relate to each other

    try this --
    Code:
    SELECT cat.category
         , COUNT(*) as videos
      FROM uc_list_of_video_parent_categories AS cat
    INNER
      JOIN uc_video_categories AS vcat
        ON vcat.parent_category = cat.id
    INNER
      JOIN uc_videos as vid
        ON vid.video_id = vcat.video_id
       AND vid.author_id = 16
    GROUP
        BY cat.category
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    20
    That works perfect, thanks! I see that you even got the count for each category for me, thank you! It'll be awhile of looking at this query before I fully understand how its constructed...there's a reason I got a D in my database class ^^ Thank you for the query!

  6. #6
    Join Date
    Dec 2009
    Posts
    20
    I've edited the query you've given me to display videos that match a certain search criteria - However, when I run the following query, it always only returns one row, even there are multiple rows that match the criteria entered. What am I doing wrong? Thanks again for your help!

    Code:
    SELECT vid.video_id, vid.youtube_id, vid.author_id, date_format(vid.creation_date, '%M %D, %Y %h:%i%p') AS formatted_creation_date, vid.video_path, vid.video_thumbnail_path, vid.video_title
    FROM uc_list_of_video_parent_categories AS cat
    INNER JOIN uc_video_categories AS vcat
    ON vcat.parent_category = cat.id
    AND cat.category = '".$search_cat."'
    INNER JOIN uc_videos as vid
    ON vid.video_id = vcat.video_id
    AND vid.video_title LIKE '%".$search_keyword."%'
    AND vid.video_live = '1'
    GROUP BY cat.category

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tlshaheen View Post
    What am I doing wrong?
    you are trying to show video details with a GROUP BY on category



    the purpose of the GROUP BY clause is to perform an aggregation -- that's why functions like COUNT, SUM and MAX are called aggregate functions

    because of the GROUP BY clause, your query will produce one row per category

    it does not matter what you put into the SELECT clause, whether it's an aggregate function (like i had in the query to count videos per category), or columns from some table, you still get only one row per group
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2009
    Posts
    20
    Perfect, thank you very much for the answer + explanation!

Posting Permissions

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