How To Get Data From 4 Tables In 1 Sql Query?

I have the following database schema: table courses: id tutor_id title table course_categories: id category_id course_id table categories: id name table tu

Solution 1:

With this query you get what you want:

select co.title as course,
       ca.name as category,
       t.name as tutor,
       count(s.*) as total_subscribers
from courses co
inner join course_categories cc on c.id = cc.course_id
inner join categories ca on cc.category_id = ca.id
inner join tutors t on co.tutor_id = t.tutor_id
left join subscribers s on co.id = s.course_id
where co.title = 'Cat1'groupby co.title, ca.name, t.name

I used left join on subscribers because there might be no one for a given course. I'm assuming that all the other tables have data on it for every course, categorie and tutor. If not, you can user left join as well but then you'll have data with null.

Solution 2:

It can be done. You need to look up select and the use of join. See select and join to help complete the assignment

Solution 3:

select cou.title, cat.name, tu.name, count(sub.user_id) from courses cou, course_categories cca, categories cat, tutors tu, subscribers sub where cou.id = cca.id and cat.id = tu.id and tu.id = sub.id group by cou.title, tu.name;