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:

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

Code (markup):

 

Assuming you only want categories and then the number of videos in each category like below:

uc_videos table
video_id author_id
1 100
2 200
3 300

uc_video_categories
video_id parent_cateogry
1 1000
2 1000
3 2000

uc_list_of_parent_categories
Parent_category_id Category
1000 Jackson
2000 Lady Gaga

select count (video_id) , category
from uc_videos v, uc_video_categories vc , uc_list_of_parent_categories pc
where v.video_id = vc.video_id
and vc.parent_category = pc.parent_category_id
and author_id = ?
group by category

Shouldn't this give you what you want? Let me know if I have made any wrong assumptions...Btw the '?' is where you'll enter your author_id value.

 

Thanks for the reply! I'm not sure if yours works the way I needed it to or not, someone on another forum gave me the answer I need just about at the same time as you did. For your reference:

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

Code (markup):

 

I think mine should work too. I don't think you have to specifically state inner join, it can be done implicitly.

I think both queries are same, except for the second one explicitly tells you that it is doing an inner join, whereas mine doesn't state that. Note that both do conditional statements on same fields.

Just for your reference.

 http://en.wikipedia.org/wiki/Join_(SQL)

Code (markup):

Search for "implicit join notation" in that page. You'll get it.

Can you just try mine and let me know if it works... I'm already home... and don't have dB connectivity here.

 

I was wondering whether you had to explicitly say inner join, since I've seen it both ways.
I had to change some field names around, and the space between count and (video_id) was causing an error - as well as I had to say which video_id field I wanted. But yes, after those changes, it came out to the same. Thanks!

With the changes:

select count(vc.video_id) AS videos , category
from uc_videos v, uc_video_categories vc , uc_list_of_video_parent_categories pc
where v.video_id = vc.video_id
and vc.parent_category = pc.id
and author_id = 16
group by category 

Code (markup):

 

Kewl... was just wondering... Ty buddy.. glad it worked !