MySQL + PHP 同じテーブルを対象にした複雑なクエリ

質問

こんばんは。
Mysql+php環境で、以下のようなテーブルを持っています。
ちょっと複雑な内容でクエリ結果を求めたいのですが、
うまい方法が見つからず、質問させて頂きます。
――――――――――――――――
▽ユーザーテーブル
――――――――――――――――
user_id ｜user_name
1 ｜タロウ
2 ｜ヨシコ
3 ｜シゲル
4 ｜マユミ
――――――――――――――――
▽所有果物テーブル
――――――――――――――――
goods_id ｜goods_name ｜user_id
1 ｜パイン ｜1
2 ｜みかん ｜1
3 ｜メロン ｜1
4 ｜リンゴ ｜2
5 ｜みかん ｜2
6 ｜イチゴ ｜2
7 ｜グレープ ｜3
8 ｜イチゴ ｜3
9 ｜キウイ ｜3
10 ｜リンゴ ｜4
11 ｜みかん ｜4
12 ｜メロン ｜4
――――――――――――――――
▽出力したい条件
――――――――――――――――
自分（例えばマユミ）が所有している果物(goods)と、
いずれか同じものを持っている人（user）が
他に持っている果物(goods)を出したいが、
自分が既に所有しているものは除く。
――――――――――――――――
今回のデータと条件ですと、
同じものを持っているuserはタロウとヨシコで、
彼らが持っていてマユミが持っていない果物は
「パインとイチゴ」になります。

このような結果を出したいのですが、
これを出力するにはどのようなSQL文にすればよいでしょうか？
実際のデータはもっとレコード数が多いテーブルです。
サブクエリを駆使しても、どうも思った結果が出ず・・・

どうかお助け下さい。
宜しくお願いします。

回答No.2

noname#98692 · ベストアンサー

テーブルの形がよくないので、以下のように作り直ししたとします。

CREATE TABLE user (
id int(10) unsigned NOT NULL auto_increment,
name varchar(15) NOT NULL default 'no name',
PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE item (
id int(10) unsigned NOT NULL auto_increment,
name varchar(15) NOT NULL default 'no name',
PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE owner (
id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned NOT NULL,
item_id int(10) unsigned NOT NULL,
count int(10) unsigned NOT NULL,
PRIMARY KEY (id),
KEY (user_id),
KEY (item_id)
) DEFAULT CHARSET=utf8;

+--+--------+
| id .| name　　. |
+--+--------+
|　1 | タロウ　 　|
|　2 | ヨシコ　 　|
|　3 | シゲル 　 |
|　4 | マユミ　 　|
+--+--------+
+--+--------+
| id .| name　　. |
+--+--------+
|　1 | パイン 　 .|
|　2 | みかん　　|
|　3 | メロン　　 |
|　4 | リンゴ　　 |
|　5 | イチゴ　　 |
|　6 | グレープ　|
|　7 | キウイ　　.|
+--+--------+
+--+------.+------+-----+
| id .| user_id | item_id | count |
+--+------.+------+-----+
|　1 |　　　 1　|　　　 1　|　　 1　|
|　2 |　　　 1　|　　　 2　|　　 1　|
|　3 |　　　 1　|　　　 3　|　　 1　|
(略)

SELECT
user.name AS もらう人,
(SELECT u.name FROM user AS u WHERE owner.user_id = u.id) AS あげる人,
(SELECT item.name FROM item WHERE owner.item_id = item.id) AS もらうアイテム
FROM user, owner
WHERE
user.id != owner.user_id
AND owner.count > 0
AND owner.item_id NOT IN (SELECT o1.item_id
FROM owner AS o1
WHERE user.id = o1.user_id)
AND owner.user_id IN (SELECT DISTINCT o2.user_id
FROM owner AS o1, owner AS o2
WHERE o1.user_id != o2.user_id
AND o1.item_id = o2.item_id
AND o1.user_id = user.id)
ORDER BY user.id

多分これで目的にあってると思います。

+-------------+------------+---------------------+
| もらう人　　　　　 | あげる人　　　　| もらうアイテム　　　 　 .　 　|
+-------------+------------+---------------------+
| タロウ　　　　　　 | ヨシコ　　　　　　| イチゴ　　　　　　　　　　　　　|
| タロウ　　　　　　 | ヨシコ　　　　　　| リンゴ　　　　　　　　　　　　　|
| タロウ　　　　　　 | マユミ　　 　 　 .| リンゴ　　　　　　　　　　　　　|
| ヨシコ　　　　　　 | タロウ　　　　　　| パイン　　　　　　　　　 　　　.|
| ヨシコ　　　　　　 | タロウ　　　　　　| メロン　　　　　　　　　　　　　|
| ヨシコ　　　　　　 | シゲル　　　　　 | キウイ　　　　　　　　　　　　　|
| ヨシコ　　　　　　 | マユミ　　　　　　| メロン　　　　　　　　　　　　　|
| ヨシコ　　　　　　 | シゲル　　　　　 | グレープ　　　　　　　　　　　 |
| シゲル　　　　　　| ヨシコ　　　　　　| リンゴ　　　　　　　　　　　　　|
| シゲル　　　　　　| ヨシコ　　　　 　 | みかん　　　　　　　　　　 　　|
| マユミ　　　　　　 | ヨシコ 　 　 　 　| イチゴ　　　　　　　　　　　 　 |
| マユミ　　　　　　 | タロウ　　 　　　.| パイン　　　　　　　　　　　.　 |
+-------------+------------+---------------------+
12 rows in set (0.01 sec)

回答No.3

noname#98692

No1 へのお礼と入れ違えてしまいました。始めの質問だけ見て解答を書いてしまいました。テーブルを作り直す所はいらなかったですね。

果物だけのマスタがあるようなので、このまま適用できると思います。(テーブル名などは書き換える必要がありますが)

お礼

ありがとうございます！！
見易いようにお気遣いもいただき感謝しております！
後ほど検証結果を報告させいただきます。
取り急ぎお礼まで。

補足

ephysさん、いただいた内容で実際にやってみたところ、
見事に求めていたデータが出力されました！
ありがとうございました！

しっかり理解して今後に活かしたいと思います。
本当にありがとうございました！

回答No.1

yambejp

このテーブル構造で確定という前提でしょうか？
正直SQLとしてはかなりだめな部類の書き方をしています。
（たとえば、IDとは普通ユニークなキーに割り当てるものですが、
IDとしているにもかかわらず、今回の命題は冗長なデータの持ち方を
しています）
テーブル構造を見直せばだいぶ検索性もあがると思います

お礼

ありがとうございます。
一部説明を省略してしまっておりましたが、
実際は「果物テーブル」が別にありました。
つまり、
k_id｜k_name｜
1｜パイン｜
2｜みかん｜
3｜メロン｜
4｜リンゴ｜
・・・

と、果物が別マスタで管理されており、
「所有果物テーブル」は全て数値のみのデータとなっています。

ということでテーブル構成としては、
「ユーザーマスタ」
「果物マスタ」
「どのユーザーがどの果物を持っているか」
という構成になっています。

また細かいですが、所有果物テーブル内のuser_idはgoods_user_idというフィールド名でした。

このテーブル構成では、望んでいるクエリを行うことは難しいということでしょうか。。