CODEWITHSUNDEEP
mysqlleft-join
Ape
Ape

Reputation: 58

LEFT JOIN not returning NULL rows

A question with this title exists a number of times but I am having a hard time figuring it out in regards to my table.

I have a table with courses

id     |    name
1      |    course A
2      |    course B

and a table with videos for the courses

course_id    |     name
1            |     video A
1            |     video B

I want to retrieve all courses including how many videos they have. My query only returns course A but not course B (where there are no videos). But I still want course B to be returned

SELECT c.name, COUNT(v.course_id) as num_videos FROM courses as c 
LEFT JOIN videos as v ON v.course_id = c.id

Upvotes: 0

Views: 69

Answers (1)

Rahul
Rahul

Reputation: 77846

You are missing a group by here

SELECT c.name, COUNT(v.course_id) as num_videos 
FROM courses as c 
LEFT JOIN videos as v ON v.course_id = c.id
GROUP BY v.course_id;

Upvotes: 3

Related Questions