SQL statement, JOIN multiple table join table query problem

subject table, which can be understood as a classification table. The
course and source_book tables are associated with subject
now use the subject table as the main table to query how many courses and books are under this category.

query statement 1: query the number of courses under each category

SELECT
    `subject`.sid,
    COUNT( book.id ) AS book_count
FROM
    `subject`
    LEFT JOIN `book` ON `subject`.`sid` = `book`.`sid` 
GROUP BY
    `subject`.`sid`

< table > < thead > < tr > < th > sid < / th > < th > book_count < / th > < / tr > < / thead > < tbody > < tr > < td > s@_5a61cb6e8d76c11548vIrM < / td > < td > 4 < / td > < / tr > < tr > < td > s@_5a65bd36d97902207DvomQ < / td > < td > 0 < / td > < / tr > < tr > < td > s@_5a65bd36dc7ec2207LdZAM < / td > < td > 0 < / td > < / tr > < tr > < td > s@_5a65bd36deea72207Flx2K < / td > < td > 0 < / td > < / tr > < tr > < td > s@_5a65bd36e3cae22075HcSq < / td > < td > 2 < / td > < / tr > < tr > < td > s@_5a65bd36f26dc2207Y9s5m < / td > < td > 1176 < / td > < / tr > < / tbody > < / table > < hr >

so how do you combine these two sql statements to get such a result?

< table > < thead > < tr > < th > sid < / th > < th > course_count < / th > < th > book_count < / th > < / tr > < / thead > < tbody > < tr > < td > s@_5a61cb6e8d76c11548vIrM < / td > < td > 5 < / td > < td > 4 < / td > < / tr > < tr > < td > s@_5a65bd36d97902207DvomQ < / td > < td > 10 < / td > < td > 0 < / td > < / tr > < tr > < td > s@_5a65bd36dc7ec2207LdZAM < / td > < td > 52 < / td > < td > 0 < / td > < / tr > < tr > < td > s@_5a65bd36deea72207Flx2K < / td > < td > 34 < / td > < td > 0 < / td > < / tr > < tr > < td > s@_5a65bd36e3cae22075HcSq < / td > < td > 143 < / td > < td > 2 < / td > < / tr > < tr > < td > s@_5a65bd36f26dc2207Y9s5m < / td > < td > 1200 < / td > < td > 1176 < / td > < / tr > < / tbody > < / table >

Mysql sql

Feb.26,2021

SELECT subject.sid, (SELECT COUNT(course.id) AS course_count FROM course WHERE course.sid = subject.sid), (SELECT COUNT(book.id) as book_count FROM book WHERE book.sid = subject.sid) FROM subject;

SELECT sid, SUM(course_count) AS course_count, SUM(book_count) AS book_count FROM ( SELECT `subject`.sid, COUNT( course.id ) AS course_count, 0 AS book_count FROM `subject` LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid` GROUP BY `subject`.`sid` UNION ALL SELECT `subject`.sid, 0 AS course_count, COUNT( book.id ) AS book_count FROM `subject` LEFT JOIN `book` ON `subject`.`sid` = `book`.`sid` GROUP BY `subject`.`sid`) A GROUP BY sid

--sqlserver -- -- SELECT [subject].Id,COUNT([course].Id) AS course_count, COUNT([book].Id) AS book_count FROM [subject],[course],[book] WHERE [course].Uid=[subject].Id AND [book].Uid=[subject].Id GROUP BY [subject].Id

SQL statement, JOIN multiple table join table query problem

so how do you combine these two sql statements to get such a result?

method 1: