【DB】不合格が1科目でもある場合抽出されないようにしたい

Question

前提・実現したいこと

データベース設計についての質問です。
生徒の受講記録テーブルがあります。

ログID	氏名	教科ID	教科名	科目ID	科目名	合否
1	太郎	1	理科	1	化学	1
2	太郎	1	理科	2	物理	0
3	太郎	1	理科	3	生物	1
4	花子	1	理科	1	化学	1
5	花子	1	理科	2	物理	1
6	花子	1	理科	3	生物	1

上記のようなテーブルがあるとして、
以下のようにSELECTしたいです。
・氏名、教科名、科目名をSELECT
・ただし、教科（理科）内の科目（化学、物理、生物）内で一つでも不合格（0）な科目がある場合、その教科はSELECTしない

つまり、物理が不合格な太郎くんは教科が全て修了していないとして抽出せず、

氏名	教科名	科目名
花子	理科	化学
花子	理科	物理
花子	理科	生物

上記のような結果を出したいです。

ですが、どういったWHERE文を書けばいいのかわかりません。
この結果を出せるようなWHERE文（またはGROUP BY ~ HAVING）を教えてください。

該当のソースコード

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)

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

SQL Serverを使用しています。

ここにより詳細な情報を記載してください。

Comments

[m.ts10806]

まず自身が思ったように組んでみてください。
テーブル定義もサンプルデータもコピペで構築できるように提示されてない状態では的確なアドバイスにはなり得ないかと

[UShow]

修正依頼ありがとうございます。

テーブル定義、コピペで構築できるサンプルデータがないという不便ななか、ご回答いただき誠にありがとうございました。
実は今回はもとのテーブルから簡便なテーブルとして例に挙げさせていただいたのでテーブル定義は上げることができませんが、サンプルデータの方は本日の夕方に上げるように致します。

大変失礼いたしました。また、ご回答誠にありがとうございました。

[m.ts10806]

回答ではなく質問への追記修正依頼です。
「本日の夕方にあげる」だと内容次第で回答変わる可能性も出てくるのでは？
解決済みにしてしまったあとの質問編集は余程のことがない限り避けたほうが良いです。

[UShow]

お世話になっております。
昨日は夕方ごろにサンプルコードを上げれず申し訳ございませんでした！
ちょっと仕事が終わらず。。。

m.ts10806様から頂いたご回答の相関副問い合わせを参考にしたところ、無事解決いたしました！

また、質問編集について今後注意していきます。

この度は誠にありがとうございました。

[m.ts10806]

私は回答していません。質問への追記修正依頼をしています。

[UShow]

すみません、寝ぼけてて人違いしてしまいました…・

[m.ts10806]

いえ怒ってるわけではないです。
様々なところに様々な人が投稿するので慣れる必要はありますしね。

Answer 1

ある生徒が受講している科目数を得る
ことができれば、その値と合否の合計数が一致していなければ弾くことができます。

ということは受講記録を氏名でgroup by したときに、
科目IDの個数（count(科目ID）)と、
合否の合計数（sum(合否)）が一致しているものだけを抽出すればよいです。

SELECT
  氏名,
  COUNT(科目ID) AS 科目数,
  SUM(合否) AS 合格数
FROM 受講記録テーブル
GROUP BY 氏名
HAVING 科目数=合格数

これで抽出すべき氏名が求まりますから、あとは「この氏名を持つ人の教科名と科目名を抽出」ですね。

Comments

[UShow]

ご回答ありがとうございます！
COUNT(科目ID)＝SUM(合否)で抽出できること、大変勉強になりました。

tacsheaven様、大変ありがとうございました。

[tacsheaven]

今回は「全部合格している人だけ」を抽出でしたが、例えば「1科目だけダメ」（追試対象）なんてのも、応用すれば抽出できます。
速度的にはおそらく sazi 様の回答の方がエレガンテなのですが、考え方を示す方がよいかと思い回答しました。

Answer 2

教科（理科）内の科目（化学、物理、生物）内で一つでも不合格（0）な科目がある場合 その教科はSELECTしない

このような場合は相関副問合せの結果をexistsで判断します。

select *
from テーブル t1
where not exists(
       select 1 from テーブル t2 
       where t2.氏名=t1.氏名 and t2.教科ID=t1.教科ID and t2.合否=0
      )

相関副問合せを使用しない場合は以下の様になります。

select t1.*
from  テーブル t1 left join (
        select 氏名, 教科ID from テーブル
        where 合否=0
        group by 氏名, 教科ID
      ) t2
      on  t2.氏名=t1.氏名 and t2.教科ID=t1.教科ID
where t2.氏名 is null

SQLServerではこちらの方がコストは低いかもしれません。

Comments

[UShow]

ご回答ありがとうございます。

NOT EXISTSを使用した相関副問い合わせ（前者の解決案）にて解決致しました。

この度はサンプルデータなどがないというご不便の中ご回答いただき、誠にありがとうございました！

自身が未熟なことを改めて痛感しましたので、頂いたご回答をもとに相関副問い合わせについて勉強致します。

ご回答ありがとうございました！