３つの表の結合から、２つの表のみのカウント数

質問

PHPとMySQLにて図書館のプログラムを作成しています。

今回の問題の箇所で必要な表は３つです。
関係のある項目のみ記述します。

bookテーブル　　　　（isbn,本番号）
reviewテーブル　　　（isbn,評価）
rentalテーブル　　　（本番号）

グループ化として、isbnでグループ化しています。

ページに必要な情報は、３つの表から必要なため、それぞれを外部結合させています。

今回、そのページでは、ソート機能としてプルダウンメニューからソートしたい項目を選択すると、それに応じてソートさせています。

五十音順、評価順、レンタル数順、新着順とありますが、その中でレンタル数のみうまくいきません。

レンタル数のみであれば、bookテーブルとrentalテーブルのみでカウントすれば正常に結果が得られますが、そこに外部結合でもう１つ表が加わっていることが原因で、正しい結果が得られません。

何か方法はありますでしょうか？、

回答No.2

yambejp

まずは、正規化されていないbookテーブルを調整した方がよいですね。
bookテーブルでは本番号というidでユニークになっていますが
実際1と2、3と4が同じなのでタイトルも同じですね。
そうなるともう一段階正規化が必要になります。
例えばbookmasterテーブルとか・・・そういう本の情報をとっておくやつ。

bookテーブルとrentalテーブルを外部結合してさらにbookmatster
テーブルに外部結合すると正式なカウントが得られます。

またreviewテーブルは外部結合するにはムリがあります。
仮にbookとrentalテーブルで集計した物にレビュー情報をつけたいなら
とくにカウントをとりたいのにレビューを外部結合してしまっては
なんのカウントなのかさっぱりわからないことになります。
別SQLでwhere isbn='111'など抽出してやるのが妥当でしょう。

お礼

具体的な改善点も指摘して頂き、ありがとうございます。

bookテーブルはほぼすべてのプログラムで使用していますので、テーブルを組みなおすのは少々時間がかかるので、すぐに試せませんが、アドバイスを参考に改善してみます。

ありがとうございました。

回答No.1

chukenkenkou

MySQLのバージョンは、何ですか？
～4.0、4.1、5.0～で多くの機能追加があり、一部、仕様変更もされています。せっかく、具体的なSQLを提示しても、質問者さんの環境では動かないかも知れません。

元の表のデータ例、得たい結果例、今考えているSQLを示してください。
文章でだらだらと書かれても、曖昧であったり、いろいろな解釈ができてしまいます。データ例やSQLを示してもらうことで、回答者側でも、「やりたいこと」を推測しやすくなります。

補足

回答ありがとうございます。
質問のアドバイスありがとうございます。

MySQLのバージョンは5.0です。

では、テーブルのデータ例です。
分かりやすくするために、必要な項目のみ、また、簡潔なデータ表記にします。項目名も分かりやすいように日本語表記にします。

bookテーブル
------------------------------------------
タイトル　　　　isbn　　　　　本番号
-----------------------------------------
あ　　　　　　　111　　　　　　　1
あ　　　　　　　111　　　　　　　2
い　　　　　　　222　　　　　　　3
い　　　　　　　222　　　　　　　4
う　　　　　　　333　　　　　　　5

reviewテーブル
------------------------------------------
point　　　　　isbn　　　　　内容
-----------------------------------------
5　　　　　　　111　　　　　　この本は…（レビュー本文）
4　　　　　　　222　　　　　　　
4　　　　　　　333　　　　　　　
3　　　　　　　111　　　　　　　
2　　　　　　　333　　　

rentalテーブル
------------------------------------------
会員　　　　　本番号　　　　　貸出日
-----------------------------------------
a　　　　　　　5　　　　　　2007/09/06
b　　　　　　　3　　　　　　2007/09/07
c　　　　　　 5　　　　　　2007/09/08
d　　　　　　　3　　　　　　2007/09/09
e　　　　　　　3　　　　　　2007/09/10

質問の部分でも記載しましたが、PHP部分で３つの表からそれぞれ情報を抽出し、ソートしたいと考えています。

その中で、うまくいかないのがレンタル数が多い順番にソートするというものです。

bookテーブルとrentalテーブルの２つの表だとうまくいきます。

select b.タイトル,b.isbn,b.本番号,count(isbn) as ccc from book b,rental r
where
　b.book_id = r.book_id
group by isbn
order by ccc desc;

得られる結果
------------------------------------------
タイトル　　　　isbn　　　　　ccc（貸し出し数）
-----------------------------------------
い　　　　　　　222　　　　　　　3
う　　　　　　　333　　　　　　　2
あ　　　　　　　111　　　　　　　0

このような結果を得たいのです。

しかし、今回、他にもソート項目があるため、３つの表を結合する必要があります。

そして、カウント数を取ると、うまくいきません。

以下、うまくいかなかったSQL文です。

select b.タイトル,b.本番号,b.isbn,count(b.isbn) ccc,TRUNCATE(avg(r.point),1) point from (book b left join review r on r.isbn = b.isbn) left join rental t on b.book_id = t.book_id

group by isbn
order by ccc desc

何か足りない情報等があれば、おっしゃってください。
お願いします。