３つのテーブルから、データが重複しないように抽出したい

質問

お世話になっております。

今回、以下のような３つのテーブルから情報を引き出したいのですが、
うまくいかず困っております。

【TABLE_A】
　a_no　|　animal_name　|
------+------------+
　0001　|　　　uma　　　|
　0002　|　　　usagi　　　|
　0003　|　　　kirin　　　|

【TABLE_B】
　b_no　|　fish_name　|
------+----------+
　000A　|　ayu　　　　　|
　000B　|　suzuki　　　　|
　000C　|　kurodai　　　|

【TABLE_C】
　NO　|　a_no1　|　a_no2　|　b_no1　|　b_no2　|
----+-------+-------+-------+-------+
　1　|　　0001　|　0003　　|　null　　|　000A　　|
　2　|　　0002　|　null　　　|　000C　|　000B　　|
　3　|　　0001　|　0002　　|　null　　|　null　　　|

TABLE_Cをベースに、A、Bのテーブルから該当データのname値を取ってきたいのですがうまくいきません。

私が求める結果は以下のような感じです。（条件：WHERE NO = 1）
　NO　|　a_no　|　animal_name　|　b_no　|　fish_name　|
-----+------+------------+------+----------+
　1　　|　0001　|　　　uma　　　　|　null　　　|　　null　　　|
　1　　|　0003　|　　　kirin　　　　|　000A　　|　　ayu　　　|

下記のように、INNER JOIN句を使ったりして四苦八苦してみたのですが、いまいち上手く抽出できません。
SELECT c.NO, a.a_no, a.animal_name, b.b_no, b.fish_name
FROM (TABLE_C c
INNER JOIN TABLE_A a
ON c.a_no1 = a.a_no
OR c.a_no2 = a.a_no )
INNER JOIN TABLE_B b
ON c.b_no1 = b.b_no
OR c.b_no2 = b.b_no
WHERE c.NO = 1;

上記だと、
　NO　|　a_no　|　animal_name　|　b_no　|　fish_name　|
----+-------+------------+------+----------+
　1　　|　0001　|　　　uma　　　|　null　　　|　　null　　|
　1　　|　0003　|　　　kirin　　　|　000A　　|　　ayu　　|
　1　　|　0003　|　　　kirin　　　|　000A　　|　　ayu　　|

のように、余計に重複して結果が返されてきてしまいます。（３行目は２行目と同じ結果なのでいらないのです・・・）

何か良い構文はありませんでしょうか？；；
宜しくお願い致します！

（環境は、MySQL 4.1.1です。）

回答No.2

nda23 · ベストアンサー

1レコードから2項目（行）なので、ユニオンでしょう。
また、結合元にNullがあるので、Left Join でないとレコードが抜けます。

SELECT C.NO,A.a_no,A.animal_name,B.b_no,B.fish_name
　　FROM (TABLE_C C LEFT JOIN TABLE_A A ON C.a_no1=A.a_no)
　　LEFT JOIN TABLE_B B ON C.b_no1=B.b_no
UNION
SELECT C.NO,A.a_no,A.animal_name,B.b_no,B.fish_name
　　FROM (TABLE_C C LEFT JOIN TABLE_A A ON C.a_no2=A.a_no)
　　LEFT JOIN TABLE_B B ON C.b_no2=B.b_no

お礼

回答ありがとうございます！
UNION句がありましたね・・・
下記のような感じで実現できました！
SELECT C.NO,A.a_no,A.animal_name,B.b_no,B.fish_name
　　FROM (TABLE_C C INNER JOIN TABLE_A A ON C.a_no1=A.a_no)
　　INNER JOIN TABLE_B B ON c.b_no1=B.b_no
　　WHERE C.NO = 1
UNION
SELECT C.NO,A.a_no,A.animal_name,B.b_no,B.fish_name
　　FROM (TABLE_C C INNER JOIN TABLE_A A ON C.a_no2=A.a_no)
　　INNER JOIN TABLE_B B ON C.b_no2=B.b_no
　　WHERE C.NO = 1

なぜかleft joinを使うと、NO以外すべてnullという不思議な列が出てきてしまったので、inner joinを使いました(@_@)

追加質問なのですが、
今回のように、union句でselect文を繋げて一回のSQLでデータを取得する方法と、
php側で(LAMP環境です)TABLE_AとTABLE_Cを比較、その後TABLE_AとTABLE_Cを比較、というように２度SQLを投げるのと
どちらがパフォーマンス的には良いのでしょうか？

実は、実環境では、
a_no1, a_no2, a_no3......a_no8
b_no1, b_no2, b_no3......b_no8
のように、８個ずつ値を取らなければならず、
UNIONで繋げようとした場合、すごく長いSQL構文になりそうで・・・。

また、何か他に良い方法等ございましたらご教授下さい。
よろしくお願い致します！

回答No.4

nda23

>どちらがパフォーマンス的には良いのでしょうか？
普通、1回で出来ることを2回でやるのは効率が悪いに決まっています。
今回の場合も当てはまると思います。SQLが長すぎて、1回あたりの
限界をこえるような場合以外、回数の少ない方を選ぶべきです。

>実は、実環境では
業務要件がはっきり分からないので、軽率なことはいえませんが、
1レコードに類似項目を8個も持つのは如何なものかと思います。
プロジェクトの進捗が浅いなら再設計も選択肢に入れるべきかも…

お礼

何度もありがとうございます。
長文の複雑なSQLを一度に投げるのとシンプルなSQL２回投げるのでは、もしかしたらシンプルな方が・・・とか深読みしたのですが、やっぱり１回の方がベストなのですね！
私は設計に携わっていないのですが、おそらく再設計する予定もないと思うのでこのまま頑張ります。
ありがとうございました！

回答No.3

nfushi

列を行に変換するのは簡単ではありません。
No2の回答に書かれたunion(本来ならunion allを使うべき)が正解です。
今はno8までですが、将来的にno9ができてもいいようにテーブル構造を以下のようにするのが好ましいでしょう。
【TABLE_C】
　NO　|　renban　|　a_no　|　b_no　|
------+----------+--------+--------|
　1　|　　1　　|　0001　　|　null　　|
　1　|　　2　　|　0003　　|　000A　　|
　1　|　　3　　|　null　　|　null　　|
　1　|　　4　　|　null　　|　null　　|
　1　|　　5　　|　null　　|　null　　|
　1　|　　6　　|　null　　|　null　　|
　1　|　　7　　|　null　　|　null　　|
　1　|　　8　　|　null　　|　null　　|
　2　|　　1　　|　0002　　|　000C　　|
　2　|　　2　　|　null　　|　000B　　|

お礼

回答ありがとうございます。
すいません、例の書き方が悪かったです；；
一意な連番を格納するカラムも、きちんと存在しております。
ご忠告、ありがとうございました！

回答No.1

yambejp

第一にTABLE_Cの構造が冗長で、SQLとしてはメタメタな感じですが
これを構造から変える可能性はないのでしょうか？
このままだとデータ管理上もオーバーヘッドがおおきく
パフォーマンスも期待できないかと。

とりあえず、いまのままでやるなら縦軸と横軸がずれているので
ピボットテーブルをつかうことになるかなぁ・・・

お礼

回答ありがとうございます。
正直私も詳しければ改善案等出したいところなのですが、まだそこまで行きつかず；
現状のテーブルで行うしかないのです。
ピボットテーブルという方法もあるのですね・・・勉強不足です；；
少し調べてみましたが、ちょっと難しそうなので理解に時間がかかりそう・・・落ち着いたら勉強したいと思います。
ありがとうございました！