同じフィールドを複数条件指定して検索をする場合

質問

やりたいこと。
あるものに対して識別する種別と項目から個体を設定する。
複数種別の識別項目から検索をしたい。

table_a
id_a が 個体ID
id_b が 個体識別種別ID
id_c が 個体識別項目ID

table_b
s_id が 個体識別種別ID
s_name が 個体識別種別名

table_c
k_id が 個体識別項目ID
k_name が 個体識別項目名
k_sid が 個体識別種別ID

条件：
table_aは id_aとid_bでユニークな値になる。
table_cのk_idはユニーク値。
識別種別（table_b）は無制限に増える可能性がある
識別項目（table_c）も無制限に増える可能性がある
種別が可変なのでtable_aに種別分のフィールドを作ることができない。

例）
Aさんは手が二本で頭は悪い。
Bさんは手が二本で頭は良い。
Cさんは手が四本で頭は普通。
Dさんは手が複数で頭は悪い。

table_a(id_a,id_b,id_c)
A,1,1
A,2,4
B,1,1
B,2,5
C,1,2
C,2,6
D,1,3
D,2,4

table_b(s_id,s_name)
1,手
2,頭

table_c(k_id,k_name,k_sid)
1,1,二本
2,1,四本
3,1,複数
4,2,悪い
5,2,良い
6,2,普通

検索：手が二本で頭が良い人
結果：A

以上のことをやるにはどのようにしたら良いでしょうか。
テーブル構造が悪い場合変更することも検討しています。
よろしくお願いします。

回答No.2

yambejp · ベストアンサー

table_aのid_bかtable_cのk_nameを取ってください。
これがあるから混乱しているのでしょう。
検索性からいうとtable_aのid_bを取ることをお勧め
しますが、情報量をすくなくすにはtable_cのk_name
を取る手もあります。

で具体的にどうするかと言えば
＃１さんのおっしゃるようにメンバー表をつくると
あっという間でしょう。

例えばこんな感じの表
table_member(ID)
A
B
C
D

で、この場合inner join でtable_cを二回読んで
やればよいでしょう。inner joinはユニークに
呼ばなくてはいけないらしいので別名をつかって
呼びます。

select `ID` from `table_member`
inner join `table_a` AS `table_a_te`
on (`table_member`.`id`=`table_a_te`.`id_a` and `table_a_te`.`id_c`='1')
inner join `table_a` AS `table_a_atama`
on (`table_member`.`id`=`table_a_atama`.`id_a` and `table_a_atama`.`id_c`='5')

最初のjoinで手の状態をチェックして、
次のjoinで頭の状態をチェックしています。

お礼

回答ありがとうございます。

少しテストをしましたが、このやり方でできそうです。
来週くらいに詳細のテストをしてみます。
ありがとうございました。

回答No.4

webuser

要するに最終的には、、

＃２さんのように
ＩＤ固有のテーブルに条件の数だけ結合をぶら下げて取ってくるという事になります。

お礼

やることは理解できました。
どうやればいいかも悩んでいたのでとても助かりました。

詳細は来週頭辺りにテストをしてみます。
ありがとうございました。

補足

テストをしてみた結果、思う通りのデータを取得できました。
実現可能かどうかも分からなかったのでとても助かりました。
ありがとうございました。

回答No.3

webuser

＞実は、裏ではこのような処理はされています。
＞今回質問するにあたり、テーブルを複数書くと混乱するので、三つのテーブルとしています。

ですよね。
業務用の場合、項目１つに対してコードと名称がセットになってますが、話をする時には複雑になるので省いていると。。

でも、多分話を省略する時に間違えて書いてる気がします。
テーブルの構成がわからないので問い合わせを発行する段階まで考えが行かないのです。

とりあえず、コードを置いといて名称だけに正規化しますと

table_a(ID,識別種別ID,識別項目ID)
Aさん,1,1
Aさん,2,4
Bさん,1,1
Bさん,2,5
Cさん,1,2
Cさん,2,6
Dさん,1,3
Dさん,2,4

table_b(識別種別ID,識別種別名)
1,手
2,頭

table_c(識別項目ID,識別項目名,識別種別ID)
1,1,二本
2,1,四本
3,1,複数
4,2,悪い
5,2,良い
6,2,普通

この辺で行き詰るでしょ？

多分table_cのデータが識別項目名と識別種別IDが逆になってるのでしょうが・・・
だとして話を続けます。

table_c(識別項目ID,識別項目名,識別種別ID)
1,二本,1
2,四本,1
3,複数,1
4,悪い,2
5,良い,2
6,普通,2

そして識別種別IDはtable_bと共通なので名称に置き換えると

table_cX(識別項目ID,識別項目名,識別種別名)
1,二本,手
2,四本,手
3,複数,手
4,悪い,頭
5,良い,頭
6,普通,頭

table_aX(ID,識別種別名,識別項目ID)
Aさん,手,1
Aさん,頭,4
Bさん,手,1
Bさん,頭,5
Cさん,手,2
Cさん,頭,6
Dさん,手,3
Dさん,頭,4

うーん、やっぱりおかしい。
テーブルＡで識別項目持ってるなら識別種別持っても意味が無いし。。。

テーブル１
Aさん,1
Aさん,4
Bさん,1
Bさん,5
Cさん,2
Cさん,6
Dさん,3
Dさん,4

テーブル２
1,手,二本
2,手,四本
3,手,複数
4,頭,悪い
5,頭,良い
6,頭,普通

もしくは

テーブル１
Aさん,手,二本
Aさん,頭,悪い
Bさん,手,二本
Bさん,頭,良い
Cさん,手,四本
Cさん,頭,普通
Dさん,手,複数
Dさん,頭,悪い

コードを省いて考えると、このどちらかの形になると思うのですが。。

＞select * from table_a where id_c = '1' and id_c = '5';
＞と言うようなことをして B が一つだけ出てくるのが理想です。

ですから、一つだけ出すにはIDが固有なテーブルも書いた方がいいです。
仮にIDと名称だけのテーブルtable_dがあるとして考えた方が分かりやすいかもしれません。

補足

再度の回答ありがとうございます。
詳細な解説をありがとうございました。

＞テーブルＡで識別項目持ってるなら識別種別持っても意味が無いし。。。

じつは、第一の目的は質問に書いた通りですが、
第二の目的が、
「手が二本の人で、頭の種類をつけた人を全て表示する」
と言うのをやることを視野に入れてこのようなテーブルになっています。
実際のテーブルはもっといろいろなデータがあるのですが、やりたいことを単純化するためにこのようなテーブルとしました。

詳細なことを言いますと、コンビニの客層データの集計みたいなことをしたいわけです。
会計ID（table_aのID）にぶら下がって、客層種類と客層項目があります。
客層Aの項目がaだった場合の客層Bの分布や、
さらに客層Bがaだった場合の客層Cの分布といった感じです。
（会計IDから売上や売り上げた商品を集計するなども取得します。これは別件なので割愛。）

少し込み入った話になりそうでしたので、単純化して説明させていただきました。
＃２さんのようにやればできそうですので、そちらで検討しようと思います。

回答No.1

webuser

テーブル構造について

table_bは上位区分情報ですね
＜table_b＞
上位区分Key,上位区分名称
a1,手
a2,頭

問題ありません。

table_cは下位区分情報ですね
＜table_c＞
固有Key,上位区分Key,下位区分名称
b1,a1,二本
b2,a1,四本
b3,a1,複数
b4,a2,悪い
b5,a2,良い
b6,a2,普通

ここは通常は下位区分Keyも持ちますね。
＜table_c・改＞
固有Key,上位区分Key,下位区分Key,下位区分名称
b1,a1,c1,二本
b2,a1,c2,四本
b3,a1,c3,複数
b4,a2,c1,悪い
b5,a2,c2,良い
b6,a2,c3,普通

table_aは人の詳細情報ですね。
人単位の情報を検索するなら、まず「人基本情報」をつくる事から設計しては如何ですか？

＜人基本情報＞
名前Key,名前
d1,Aさん
d2,Bさん
d3,Cさん
d4,Dさん

で一番普通なのは、ここに手情報と頭情報を項目として持つ。

＜人基本情報・改＞
名前Key,名前,手情報key,頭情報key
d1,Aさん,c1,c1
d2,Bさん,c1,c2
d3,Cさん,c2,c3
d4,Dさん,c3,c1

この状態だと
select 名前 from 人基本情報・改 where 手情報key=c1,頭情報key=c2
で簡単に取得できます。

または、手と頭の情報を項目として持ちたくないなら、
人基本情報はそのままで、下位の「人詳細情報」を持つ。

＜人詳細情報＞
名前Key,上位区分Key,下位区分Key
d1,a1,c1
d1,a2,c1
d2,a1,c1
d2,a2,c2
d3,a1,c2
d3,a2,c3
d4,a1,c3
d4,a2,c1

この状態だと人基本情報にselectして、
人詳細情報側で条件抽出して名前Keyで繋げばＯＫ

※keyは色分けのためにa,b,c,dと入れています。

補足

回答ありがとうございます。

＞人単位の情報を検索するなら、まず「人基本情報」をつくる事から設計しては如何ですか？

＞＜人基本情報＞
＞名前Key,名前
＞d1,Aさん
＞d2,Bさん
＞d3,Cさん
＞d4,Dさん

実は、裏ではこのような処理はされています。
今回質問するにあたり、テーブルを複数書くと混乱するので、三つのテーブルとしています。

また、
＞で一番普通なのは、ここに手情報と頭情報を項目として持つ。

＞＜人基本情報・改＞
＞名前Key,名前,手情報key,頭情報key

も、質問の条件で記したとおり、手や頭のほかに、足や性格や目など、Keyが増減する場合があるのでつかえません。
（ユーザー側で設定を追加できるのでKeyの数が不定なのです）

今回はテーブル情報（設計）の質問と言うよりは、検索（集計）方法の質問です。
ただし、この集計を実現させるためにテーブルを変更したほうが楽な場合はそちらを採用しようかと思っています。

あと訂正ですが、
＞検索：手が二本で頭が良い人
＞結果：A
の結果は Bです。間違えました。

感じとしては
select * from table_a where id_c = '1' and id_c = '5';
と言うようなことをして B が一つだけ出てくるのが理想です。