Multiple Joins/Combining Information From 3 Tables

tlshaheen · #1 (**permalink**) 07-09-10, 18:37

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

r937 · #2 (**permalink**) 07-09-10, 18:54

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

tlshaheen · #3 (**permalink**) 07-09-10, 19:02

Attached, hope I exported right, thanks!

Edit: updated the uc_videos dump to contain a few entries

r937 · #4 (**permalink**) 07-09-10, 19:34

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

tlshaheen · #5 (**permalink**) 07-09-10, 19:55

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!

tlshaheen · #6 (**permalink**) 07-10-10, 02:46

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

r937 · #7 (**permalink**) 07-10-10, 06:39

Quote:

Originally Posted by tlshaheen

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

tlshaheen · #8 (**permalink**) 07-10-10, 07:20

Perfect, thank you very much for the answer + explanation!

Internet Services

Web Development

Digital Marketing

Consumer Tech