2つのテーブルを繋げて条件をつけて表示したい

質問

movielistとrirekiという2つのテーブルがありまして、それぞれ次のようになっています。

【movielist】
id　category　title
1　action movie1
2　action movie2
3　document movie3
4　document movie4

【rireki】
id　movieid　name
1　　　3 　 sato
2　　　1 　 kato
3　　　3　　 kato
4　　　4　　 kato

movielistテーブルからcategoryがdocumentの全データを一覧表示して、なおかつ、katoさんが借りたことのある映画には「見た!」という表示をつけたいと思っています。

movielistテーブルからcategoryがdocumentの全データを一覧表示するには、

SELECT id,title FROM movielist WHERE category　= 'document' ;

だと思うのですが、これにkatoさんが借りたことのある映画には「見た!」という表示をつけるにはどのようにしたらいいのでしょうか？

実際の表示はPHPでMYSQLからデータを取得して表示しています。

下記で試したのですが思うように表示されませんでした。

SELECT movielist.id,movielist.title,rireki.name FROM movielist LEFT JOIN rireki ON movielist.id=rireki.movieid WHERE movielist.category = 'document';

回答No.2

yambejp · ベストアンサー

たとえば・・・

SELECT m.id, m.title, SUM( IF( r.name = 'kato', 1, 0 ) ) AS mita
FROM movielist AS m
LEFT JOIN rireki AS r ON m.id = r.movieid
WHERE m.category = 'document'
GROUP BY m.id

として、mitaが1以上の場合にしるしをつけるようPHPで処理をすれば
よいでしょう。

お礼

私の初歩的ミスでした。
うまく動作しました。
ありがとうございました。

補足

すいません、ちゃんと質問を説明できていませんでした。
katoさんだけではなく、satoさんも含めた全員分の履歴（見た！）をチェックしたいと思っています。
rirekiのnameには同一データはなく、全てユニークの値です。
nameの部分を変数として扱えないかと思い、下記部分を
SUM( IF( r.name = '$namelist', 1, 0 ) ) AS mita
としてやってみたのですがうまく表示されませんでした。
具体的には
satoさんがmovie3を「見た！」になって、katoさんが「見てない」という具合です。
この補足でもうまく言葉で説明できていなかったら申し訳ありません。

回答No.1

dell_OK

単純に２行目の部分を追加すると、

SELECT movielist.id,movielist.title,rireki.name
,if(rireki.name='kato','見た！','') mita
FROM movielist LEFT JOIN rireki ON movielist.id=rireki.movieid WHERE movielist.category = 'document'

以下のようになります。

id title name mita
3 movie3 sato
3 movie3 kato 見た！
4 movie4 kato 見た！

satoさんについてはどうしたいのか、で少々変わってきます。

katoさんについてだけ表示されればいいのでしたら、４行目の部分を追加して、

SELECT movielist.id,movielist.title,rireki.name
,if(rireki.name='kato','見た！','') mita
FROM movielist LEFT JOIN rireki ON movielist.id=rireki.movieid
AND rireki.name='kato'
WHERE movielist.category = 'document'

以下のようになります。

katoさんが見ていないdocumentのデータがないと試験できませんから、
movielistに「5 document movie5」を追加しました。
rirekiにはmovie5を見たと言うデータはないものとします。

id title name mita
3 movie3 kato 見た！
4 movie4 kato 見た！
5 movie5

仕様としては、movielistからdocumentのすべてを表示し、
rirekiを参照してkatoさんが見たものであれば、
katoさんの名前と「見た！」を表示する、と言う感じです。

satoさんやkatoさんの名前は、その名前ばかりのテーブルにして、
namaeテーブル（id,name）を作り、
rirekiのnameはnamaeidとかにします。
movie、rireki、namaeのようにした方がいいような気がします。
別人で同じ名前のkatoさんが他にいたらわからなくなるからです。

お礼

私の初歩的ミスでした。
うまく動作しました。
ありがとうございました。

補足

質問が言葉足らずでした。申し訳ありません。
>satoさんについてはどうしたいのか、で少々変わってきます。
ご指摘のとおりで、全員分をチェックしたいと思っています。
またrirekiのnameはユニークの値になります。
katoさんだけでなく、satoさんも含めた全員分の履歴と全部の映画をいっしょに表示する場合は、サブクエリを利用するべきなのでしょうか。
頭がごちゃごちゃしてしまい、補足説明もうまく伝わっていないかもしれませんがよろしくお願いします。