Mysql help required
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?
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;
Thanks, but how do I display the actual count?
Also, this omits the empty categories, and doesn’t output them.
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.
count(v.vid_id) that should be doing it…
count(v.vid_id) As vid_id_count then use that to call it ont he page
Great that worked!
But how do I still get it to show the categories that have 0 items in them?
|
Great that worked!
But how do I still get it to show the categories that have 0 items in them? |
Lets see your query
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
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 |
Where is the left join?
His "join" is in the WHERE (which is identical to INNER)
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
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
Ohh man, you are right, I cant believe I forgot about that.
Thanks, that works perfectly.
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.