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 >
Feb.26,2021

I understand that it should be something like this.

use subject.sid to go to course , book tables to query their respective numbers.

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

SELECT

`c`.sid,
COUNT( c.id ) AS course_count,COUNT( book.book_count ) AS book_count

FROM

(SELECT
    `subject`.sid,
    COUNT( course.id ) AS course_count
FROM
    `subject`
    LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid` 
GROUP BY
    `subject`.`sid`) c

LEFT JOIN book ON c . sid = book . sid
GROUP BY

`subject`.`sid`
    

Thank you very much for your answers. All three methods are available. Here's a summary:

< hr >

method 1:


--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
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-7b4dfe-12c2e.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-7b4dfe-12c2e.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?