【DB】教科の持つ科目数と生徒の受講した科目数が一致しない場合SELECTしない

Question

前提・実現したいこと

以前ご質問した内容【DB】不合格が1科目でもある場合抽出されないようにしたいの発展問題です。

生徒の受講した科目が、教科が含む科目数に一致しない場合SELECTせず、
かつ教科内の科目で一つでも不合格な科目がある場合、その教科はSELECTしない（こちらは解決済）

この条件の教科履歴のVIEWを作りたいです。

まず、テーブルが２つあります。

生徒の受講した教科履歴のテーブル（Course_history）

log_id	name	course_id	course_name	subject_id	subject_name	is_passed
1	太郎	1	理科	1	化学	1
2	太郎	1	理科	2	物理	0
3	太郎	1	理科	3	生物	1
4	花子	1	理科	1	化学	1
5	花子	1	理科	2	物理	1
6	花子	1	理科	3	生物	1
7	太郎	2	数学	1	数A	1
8	太郎	2	数学	2	数B	1
9	太郎	2	数学	3	数C	1
10	花子	2	数学	1	数A	1

教科と科目の関係を表すテーブル（Course_Subject）

id	course_id	course_name	subject_id	subject_name
1	1	理科	1	化学
2	1	理科	2	物理
3	1	理科	3	生物
4	2	数学	1	数A
5	2	数学	2	数B
6	2	数学	3	数C

発生している問題

教科内の科目で一つでも不合格な科目がある場合、その教科はSELECTしないは以下のクエリで解決しています。

SELECT log_id,
		t1.name,
		t1.course_id,
		t1.subject_id,
		t1.subject_name,
		t1.is_passed
FROM Course_history AS t1
INNER JOIN (SELECT	name,
					course_id
			FROM	Course_history
			GROUP BY	name,
						course_id
			HAVING	COUNT(course_id) = SUM(is_passed)) AS t2 ON t2.name = t1.name AND t2.course_id = t1.course_id

これで結果が
|log_id|name|course_id|course_name|subject_id|subject_name|is_passed|
|:--:|:--:|:--:|:--:|:--:|:--:|
|4|花子|1|理科|1|化学|1|
|5|花子|1|理科|2|物理|1|
|6|花子|1|理科|3|生物|1|
|10|花子|2|数学|1|数A|1|
|7|太郎|2|数学|1|数A|1|
|8|太郎|2|数学|2|数B|1|
|9|太郎|2|数学|3|数C|1|

になります。

それで今回はさらに生徒の受講した科目が、教科が含む科目数に一致しない場合SELECTしない、ようにしたいので、上表のlog_id 10が省かれそれ以外が抜き出されるような条件を作りたいです。

サンプルデータ

CREATE TABLE Course_history (
log_id INT NOT NULL,
name NVARCHAR(5) NOT NULL,
course_id INT NOT NULL,
course_name NVARCHAR(5) NOT NULL,
subject_id INT NOT NULL,
subject_name NVARCHAR(5) NOT NULL,
is_passed INT NOT NULL)
INSERT INTO Course_history VALUES (1,'太郎',1,'理科',1,'化学',1)
INSERT INTO Course_history VALUES (2,'太郎',1,'理科',2,'物理',0)
INSERT INTO Course_history VALUES (3,'太郎',1,'理科',3,'生物',1)
INSERT INTO Course_history VALUES (4,'花子',1,'理科',1,'化学',1)
INSERT INTO Course_history VALUES (5,'花子',1,'理科',2,'物理',1)
INSERT INTO Course_history VALUES (6,'花子',1,'理科',3,'生物',1)
INSERT INTO Course_history VALUES (7,'太郎',2,'数学',1,'数A',1)
INSERT INTO Course_history VALUES (8,'太郎',2,'数学',2,'数B',0)
INSERT INTO Course_history VALUES (9,'太郎',2,'数学',3,'数C',1)
INSERT INTO Course_history VALUES (10,'花子',2,'数学',1,'数A',1)

CREATE TABLE Course_Subject (
id INT NOT NULL,
course_id INT NOT NULL,
course_name NVARCHAR(5) NOT NULL,
subject_id INT NOT NULL,
subject_name NVARCHAR(5) NOT NULL,)
INSERT INTO Course_Subject VALUES (1,1,'理科',1,'化学')
INSERT INTO Course_Subject VALUES (2,1,'理科',2,'物理')
INSERT INTO Course_Subject VALUES (3,1,'理科',3,'生物')
INSERT INTO Course_Subject VALUES (4,2,'数学',1,'数A')
INSERT INTO Course_Subject VALUES (5,2,'数学',2,'数B')
INSERT INTO Course_Subject VALUES (6,2,'数学',3,'数C')

試したこと

ひとまず、Course_Subjectの教科が含む科目数は3ずつというのはわかります。
この本来の教科が持つ科目数COUNT（course_id）　＝　ユーザーの受講した教科のCOUNT（course_id）になればいいと思うのですが…
そのような条件もどこに入れたらいいのかわかっていません。

SELECT COUNT(course_id)　AS count_course
FROM Course_Subject
GROUP BY course_id

course_id	count_course
1	3
2	3

補足情報（FW/ツールのバージョンなど）

SQL Serverになります。

何卒宜しくお願い致します。
ここにより詳細な情報を記載してください。

Answer 1

単純に条件を追加するだけなら、Course_Subjectの履修科目数を条件に追加すれば良いかと。

SELECT  log_id,
        t1.name,
        t1.course_id,
        t1.subject_id,
        t1.subject_name,
        t1.is_passed
FROM  Course_history AS t1
      INNER JOIN (
        SELECT  crs.name, crs.course_id
        FROM    Course_history crs
                inner join (
                  select course_id, count(*) as subject_count
                  from  Course_Subject
                  group by course_id
                ) sbj
                on  crs.course_id=sbj.course_id
        GROUP BY crs.name, crs.course_id, subject_count
        HAVING  COUNT(crs.subject_id) = SUM(is_passed) 
            and COUNT(crs.subject_id)!=subject_count
      ) AS t2 
      ON t2.name = t1.name AND t2.course_id = t1.course_id

結合するなら、HAVINGではなくて、結合後にwhere条件で指定するようにした方が可読性は上がると思います。

Comments

[UShow]

sazi様

お世話になっております！この度はご回答ありがとうございます。
INNER JOIN を入れ子にすることでGROUP BYしたものを繋げられるとは、知りませんでした

頂いたご回答を参考にさせて頂いたところ、無事解決致しました！
この度は誠にありがとうございました！