SQL テーブルの結合について

Question

クイズシステムを作りたいと思っています。
テーブルは以下のように２つ用意しています。

[quiz_t (問題マスターテーブル)]
question_id (問題ID)
question (問題)

[user_t ユーザー回答テーブル]
user_id (ユーザーID)
question_id (答えた問題ID)

問題マスターに格納されている問題を一覧に出して、ユーザーがその問題に答えたか答えてないかを一緒に表示したいと思っています。

結果の例：
[ユーザー(user01)の回答状況]
問題１ 回答済み
問題２ 未回答
問題３ 未回答
問題４ 回答済み

これを再現するにはどういうSQL文を書けばよろしいでしょうか？
答えた問題だけを表示するなら以下のSQL文で出来たのですが、答えてない問題も合わせて一緒に取得したいと思っています。

select question_id from quiz_t inner join user_t using(question_id) where user_id='user01';

答えた問題のみユーザー回答テーブルに格納されているものとします。
内部結合か外部結合で出来るものなのでしょうか？
よろしくお願いします。

Answer 1

LEFT JOINで回答済みの問題を結合して
結合できれば回答済み、結合できなければ未回答でいいと思います。

SELECT
	  quiz_t.question_id
	, CASE
		WHEN user_t.user_id IS NULL THEN '未回答'
		ELSE '回答済み'
	  END status
FROM quiz_t
LEFT JOIN user_t
ON user_t.question_id = quiz_t.question_id
AND user_t.user_id = 'user01'

Comments

[harukitchen]

ご回答いただきありがとうございます！
上記のSQL文で目的のものが得られました。
ONの後にANDで繋げる事によって出来るのですね。where句に書いてしまってはまっていました。ありがとうございました。

Answer 2

NULLをアプリケーションで扱いたくなければSQLで変換しておくことも可能です。

select
 Q.question_id,
 CASE WHEN U.user_id IS NULL THEN '未回答' ELSE '回答済み' END AS `status`
from quiz_t Q
left join user_t U using(question_id)
where user_id='user01';

Comments

[harukitchen]

ご回答ありがとうございます。
上記のSQL文を実行してみましたが、回答済みの結果しか得られませんでした。
テーブルの設計が良くないのでしょうか。

ユーザー回答テーブルには
user01 1
user01 4
user02 2
user02 4

というようなデータを格納しています。

[kutsulog]

結合した後にユーザーで絞ってしまうと
未回答の問題はuser_idがNULLになっているため取得できず
結局INNER JOINをしているのと変わらなくなってしまいます
未回答も取得したかったら結合条件でuser_idを指定する必要があります

[harukitchen]

ありがとうございます。
where句ではなく結合条件でuser_idを指定するのですね。
試したところそれでうまくいきました。
３番目の回答の方を先に見てそれでうまくいったのでベストアンサーにできず申し訳ありません。
ありがとうございました！

[dupont_kedama]

そうでしたね。お恥ずかしい。

Answer 3

left joinを利用すれば良いと思います。
以下を参照
http://www.pursue.ne.jp/jouhousyo/SQLDoc/select22.html

また、回答なしという表示をする場合は、selectの項目でnullだった場合という条件を記載します。
これは利用しているDBMSにより変わります。

Comments

[harukitchen]

ご回答ありがとうございます。
left joinを用いたSQL文は以下となるのでしょうか？
select question_id from quiz_t left join user_t using(question_id) where user_id='user01';

この場合も内部結合と同じ結果しか得られませんでした。
where句でuser01というIDを指定しなかったら全ての問題が取得できるのですが、今回はユーザーIDを指定しています。
DBMSはMySQLを使用しております。