SQL statement, JOIN multiple tables join table query problem

  mysql, question

Subject table can be understood as classification table.
Court and source_book are associated with subject respectively.

Now we will use the subject table as the main table to query how many courses and books there are under this category.

  • Query Statement 1: Query the number of courses under each category
Select
 `subject`.sid,
 COUNT( course.id ) AS course_count
 From
 `subject`
 LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid`
 GROUP BY
 `subject`.`sid`

Query results:

sid course_count
s@_5a61cb6e8d76c11548vIrM 5
s@_5a65bd36d97902207DvomQ 10
s@_5a65bd36dc7ec2207LdZAM 52
s@_5a65bd36deea72207Flx2K 34
s@_5a65bd36e3cae22075HcSq 143
s@_5a65bd36f26dc2207Y9s5m 1200
  • Query Statement 2: Query the number of books 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`
sid book_count
s@_5a61cb6e8d76c11548vIrM 4
s@_5a65bd36d97902207DvomQ 0
s@_5a65bd36dc7ec2207LdZAM 0
s@_5a65bd36deea72207Flx2K 0
s@_5a65bd36e3cae22075HcSq 2
s@_5a65bd36f26dc2207Y9s5m 1176

So, how do you combine these two sql statements to find such a result?

sid course_count book_count
s@_5a61cb6e8d76c11548vIrM 5 4
s@_5a65bd36d97902207DvomQ 10 0
s@_5a65bd36dc7ec2207LdZAM 52 0
s@_5a65bd36deea72207Flx2K 34 0
s@_5a65bd36e3cae22075HcSq 143 2
s@_5a65bd36f26dc2207Y9s5m 1200 1176

I understand that this should be roughly the case.

Usesubject.sidGo separatelyCourse,BookTable queries the respective quantities.

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;

Related Posts: