質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.61%

  • SQL

    2330questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    1037questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

中間テーブルを2つまたがるリレーションがある時適切な検索ヒットをしたい。

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 647

acre_maker

score 131

前提

  • Table A
  • Table B
  • A, Bの中間テーブルであるTable AB
  • Table C
  • B, Cの中間テーブルであるTable BC
  • Table Bのレコードを2個あるいは3個のみがTable Cの1つのレコードとリレーションを
    持つように登録している。

中間テーブル以外にはPKがあり(ここでは便宜上A_id, B_id, C_idとします)
中間テーブルは例えばTable ABならA_id, B_idというカラムのみを持ち、
Unique(A_id, B_id)と設定されています。

実現したいこと

A_idを決めた時(例えばA_id=1とした時)
それとリレーションのあるTable AB→Table B→Table BC→Table Cとたどり、
最終的にリレーションのあるTable Cの数をカウントしたいです。

追記
例えば以下の例では

A_id=1に関しては、Table CのC_id=500, 501, 502の3つを検索ヒットし
C_id=503, 504は検索ヒットしたくありません。
(A_id=1はB_id=33, 34とリレーションがないため)

A_id=2に関しては、Table CのC_id=500の1つを検索ヒットし
C_id=501, 502, 503, 504は検索ヒットしたくありません。
(A_id=2はB_id=32, 33, 34とリレーションがないため)

A_id=3に関しては、Table CのC_id=503, 504, 505の3つを検索ヒットし
C_id=500, 502は検索ヒットしたくありません。
(A_id=3はB_id=30, 32とリレーションがないため)

発生している問題・エラーメッセージ

追記
単純に中間テーブルを用いたSQL文を以下のように考えました。

SELECT COUNT(DISTINCT(C_id)) FROM BC WHERE B_id in (
 SELECT B_id FROM AB WHERE A_id = 1
)

しかし、この自分で考えたSQL文では
以下のようなリレーションがある時、A_id=2を満たすBのB_idを取得して、
そのB_idを含むTable Cという単純な条件で検索をすると(31, 502)のレコードも
ヒットしてしまいます。
しかし、Table BCのレコード(31, 502), (32, 502)はA_id=1由来で登録したレコードなので、
ヒットしたくありません。

Table A

A_id value
1 foo
2 baz
3 bar

Table B

B_id value
30 -1
31 -2
32 -3
33 -4
34 -5

Table AB

A_id B_id
1 30
1 31
1 32
2 30
2 31
3 31
3 33
3 34

Table C

C_id value
500 Alice
501 Bob
502 Chris
503 Dylan
504 Emily
505 Franc

Table BC

B_id C_id
30 500
31 500
30 501
32 501
31 502
32 502
31 503
33 503
31 504
34 504
33 505
34 505

どのようにすれば、私が望むように検索ヒットすることができるでしょうか?
このテーブルにこだわる必要はありませんので、
そもそもテーブルの構築があまり良くないと言ったアドバイスでも結構です。
ご指導ご鞭撻よろしくお願いします。

補足情報

PostgreSQL (version9.5.7)

追記

専門的なことをしているので、抽象的に書いたほうがわかりやすいと思いましたが、
かえってわかりにくいという印象を与えてしまったようなので、
具体的なテーブルも下に書いておきます。

化学構造のデータを扱っていまして

A: 化学構造を含むライブラリ(データベース)名
B: 化学構造自体のレコード
AB: 複数のライブラリが同じ化学構造を含むことがあるためこのような形でリレーション
C: Bの化学構造のうちの2あるいは3個を組み合わせた新規の化学構造
BC: Cの新規の化学構造のために組み合わせたBの化学構造

となっております。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • acre_maker

    2017/10/03 17:21

    そうですね。作成時のみわかるので本来は作成時に何かしらの情報を残すべきだったと思っています。

    キャンセル

  • A.Ichi

    2017/10/03 17:26

    そうであれば、Table BCに作成のA_idを加える事で、SELECTでの排他条件に加える事ができるのではと考ました。

    キャンセル

  • acre_maker

    2017/10/03 21:36

    そういうのがあるのですね。排他制御というものでしょうか?勉強してみます。

    キャンセル

回答 3

checkベストアンサー

+1

Table BCのレコード(31, 502), (32, 502)はA_id=1由来で登録したレコードなので

このことが分かる条件が無い限り無理でしょう。
c_idが識別可能なルールで採番されているなら条件として使用できますが、
そうでなければ、table cにその識別が必要かと思います。

追記

以下の構造で十分な気がします。

TableA(A_id,Value)
TableB(B_id,Value,A_id[])
TableC(C_id,Value,A_id[])
TableBC(B_id,C_id)


※BCの組み合わせがあるので、BC追加。

この構造であれば、目的の件数取得は以下になります。

select count(*) from TableC where A_id @> array[1]


配列の個数をA_idと対比させ固定するなら以下のようになります。

select count(*) from TableC where A_id[1]=1
--A_id[]をBoolean型とするなら
select count(*) from TableC where A_id[1]

追記2

以前提案していた構造を今回のもので表すと

TableA(A_id,Value)
TableB(B_id,Value,B_id[],A_id[])


となります。
TableBはBとCを合わせたもので、B_id[]が分子構造の構成要素になります。
元々のTableBの識別が必要なら、属性項目を持たせた方が良いでしょう。
基本となる構成要素も検索対象にするならB_id[]にはB_idも含めます。
含めないならB_id[]は空にしておけば検索対象にはなりません。

で、B_id[]のGinインデックスを作成し、

where B_id[]@>array[30,32] -- B_id[]は便宜上の表現


のようにすれば良いのではないかと。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/02 19:43

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

    >このことが分かる条件が無い限り無理でしょう。

    (1)A_id=2の条件よりTable ABからB_id=30, 31 ((a)とします)を検索する。
    (2)(a)の条件とTable BCからC_id=500, 501, 502を検索する。
    (3)Table BC上で502に対してB_id=31, 32をみつけるけど、
    32は(a)に含まれていないから502は除外する。
    としたら行けそうな気がしましたけどSQL文では難しい工程でしょうか?


    >c_idが識別可能なルールで採番されているなら条件として使用できますが、
    >そうでなければ、table cにその識別が必要かと思います。

    例えば、Table BCにA_idを外部キーとしてカラムを追加するといった
    解決策がよいでしょうか?

    キャンセル

  • 2017/10/02 20:15

    つまり検索したいのは「A_id=2と関係があるBすべてに関係があるC」ということですか?

    キャンセル

  • 2017/10/02 20:33

    A_idは2件だけなのでしょうか?3件以上の場合はその排他の条件はどうなるのでしょう?

    キャンセル

  • 2017/10/02 21:29 編集

    >karamarimo様
    おっしゃるとおりになります。

    >sazi様
    A_idは3件以上になる予定です。
    私の理解不足のせいか、2件が3件以上になって、
    考慮しなければならない点がみえていません。
    排他の条件とはそちらを指しているのでしょうか?
    より詳しく教えていただけたら助かりす。

    キャンセル

  • 2017/10/03 02:19

    >(3)Table BC上で502に対してB_id=31, 32をみつけるけど、32は(a)に含まれていないから502は除外する。

    その条件がid=3に含まれるとどうなりますか?
    id毎に自身以外のIDにないものの件数を取得したいということ?

    キャンセル

  • 2017/10/03 06:58

    お返事ありがとうございます。

    id=3に含まれる場合もヒットしてもいいです。
    あくまで注目しているA_idとリレーションがあるBから構成されていたらいいです。

    テーブルのデータ数を増やして各A_idの時
    どうヒットしてほしいか追記してみました。
    お暇な時にご確認ください。

    キャンセル

  • 2017/10/03 11:41

    >A: 化学構造を含むライブラリ(データベース)名
    >B: 化学構造自体のレコード
    >AB: 複数のライブラリが同じ化学構造を含むことがあるためこのような形でリレーション
    >C: Bの化学構造のうちの2あるいは3個を組み合わせた新規の化学構造
    >BC: Cの新規の化学構造のために組み合わせたBの化学構造
    それぞれのライブラリごとに化学構造が幾つあるかカウントしたいということなのですね。
    それ以外の目的としてはそのデータがどのライブラリからの由来であるかどうかでしょうから、
    最終的に参照できれば良いかと思うのですが、ライブラリも検索条件になりますか?
    検索対象としての頻度が低いならBやCに配列として持たせても十分だと思いますが。

    キャンセル

  • 2017/10/03 13:01

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

    >それぞれのライブラリごとに化学構造が幾つあるかカウントしたいということなのですね。
    >それ以外の目的としてはそのデータがどのライブラリからの由来であるかどうかでしょうから、
    >最終的に参照できれば良いかと思うのですが、ライブラリも検索条件になりますか?
    >検索対象としての頻度が低いならBやCに配列として持たせても十分だと思いますが。

    おっしゃるとおりですね。
    各ライブラリから作成した新規の化学構造が何個あるか確認したいです。

    その後、新規の化学構造を以前アドバイスいただいたように別のライブラリに対して部分構造検索する
    と言った流れになっています。
    (話がそれますがこちらを先に作らないと前アドバイスいただいた式によるインデックスが試せません)

    >ライブラリも検索条件になりますか?
    ライブラリは基本的に検索条件にはなりません。
    A_id = 1, 2, 3, ...のいずれかで絞り込むだけですね。

    >検索対象としての頻度が低いならBやCに配列として持たせても十分だと思いますが。

    メインは上にかいた部分構造検索を行うため、
    基本的に確認などで検索するだけなのでおっしゃるとおり頻度は低いです。

    配列として保存するならTable BCを

    B_id | C_id |
    ----------------------
    [30, 31] | 500
    [30, 32] | 501
    [31, 32] | 502
    [31, 33] | 503
    [31, 34] | 504
    [33, 34] | 505

    と保存することが考えられますがこちらはあまり好ましくありませんか?
    これなら2,3つの組み合わせどちらにも対応できそうですが。

    お時間あるときにお答えいただけると助かります。

    キャンセル

  • 2017/10/03 14:13

    追記ありがとうございました!
    自身で紙の上でテーブルを書いてみて意図が理解できました。

    追記2でご提示してもらっているものがしっくりきました。

    >基本となる構成要素も検索対象にするなら
    はい。質問には書いていませんでしたが、
    基本構成1つからなるものも含めるにこしたことはないのでこちらを採用します。

    ARRAY_LNEGTH(B_id[ ], 1)を使えばいくつの構成要素からなるかで
    絞り込みもできますしよさそうですね。

    長々とありがとうございました!

    キャンセル

  • 2017/10/03 15:46 編集

    構成要素からその構成を参照する用途がある場合は、unnest(B_id[])のように配列を行に直してから、結合するのが効率良いかと思います。
    また、この構造が親子での2階層であればよいのですが、多階層で、階層を辿る必要があるような場合は、自己結合用の項目を(親のB_id)を持つと共に再帰問い合わせ(WITH RECURSIVE)を用い、B_id[]に含まれるレベルを揃えるなどの工夫が必要になることが予想されます。

    キャンセル

  • 2017/10/03 18:00

    >自己結合用の項目を(親のB_id)
    これは、十分ではないし、B_id[]で肩代わりできるので不要でした。

    キャンセル

0

質問に記載のデータ構造に不足しているのは、A_id = 1 の場合にABテーブルから得られるB_idの組(30,31,32)が(30,31),(30,32),(31,32)の組み合わせとならなければいけないという意味付けです。

B_id 2つにより C_id が特定できるという情報を持っているのはBCテーブルですので、BCテーブルの定義を下記とすることで特定が可能になります。

BC (B_id1, B_id2, C_id)

上記定義に変更した場合、下記のように取得できるのではないかと思います。

SELECT C_id FROM BC
INNER JOIN AB AB1 ON BC.B_id1 = AB1.B_id
INNER JOIN AB AB2 ON BC.b_id2 = AB2.b_id
WHERE AB1.A_id = AB2.A_id AND AB1.A_id = 1;

追記:
せっかく具体例をご提示いただいたのですが、上手な方法は思いつきませんでした。
後々のことを考えると列を増やすのは確かに得策ではないと感じましたので
もともとのデータ構造で実現するとしたら、ということで下記のように考えてみました。
もしかしたら、組み合わせの列挙方法がご想定のものとは異なるかもものになっているかもしれません。

SELECT BC1.c_id FROM BC BC1
INNER JOIN BC BC2 ON BC1.C_id = BC2.C_id AND BC1.B_id < BC2.B_id
 LEFT JOIN BC BC3 ON BC1.C_id = BC3.C_id AND BC2.B_id < BC3.B_id
INNER JOIN AB AB1 ON BC1.B_id = AB1.B_id
INNER JOIN AB AB2 ON BC2.B_id = AB2.B_id
 LEFT JOIN AB AB3 ON BC3.B_id = AB3.B_id
WHERE (BC3.c_id IS NOT NULL OR BC1.c_id NOT IN (
      SELECT BC1.c_id FROM BC BC1
      INNER JOIN BC BC2 ON BC1.C_id = BC2.C_id AND BC1.B_id < BC2.B_id
      INNER JOIN BC BC3 ON BC1.C_id = BC3.C_id AND BC2.B_id < BC3.B_id
   ))
  AND AB1.A_id = AB2.A_id
  AND
  (
      (BC3.B_id IS NOT NULL AND AB3.B_id IS NOT NULL AND AB1.A_id = AB3.A_id)
   OR (BC3.B_id IS     NULL AND AB3.B_id IS NULL)
  )
  AND AB1.A_id = 1;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/03 09:57

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

    2つの時はおっしゃるSQL文でうまくとりだすことができそうですね!

    少しわかりにくいですが
    >Table Bのレコードを2個あるいは3個のみがTable Cの1つのレコードとリレーションを
    持つように登録している。

    という条件で3個の場合があります。
    この場合を考慮するとどうなりますかね?
    この場合を考慮するために現在のテーブルみたいにしていたのですが
    それだとまたもとに戻ってしまいますしね。

    キャンセル

  • 2017/10/03 10:29

    BCテーブルのB_id列を3つに増やすか、3つ組の関連テーブルを増やして同様のことをすればよいかと思います。
    2つの場合、3つの場合はUNIONで結合する形になります。

    ご質問の内容は抽象化されており、A_id、B_id、C_idの関連がわかりませんので、実ケースでのよりよいデータの持ち方はすぐには思いつきませんでした。

    キャンセル

  • 2017/10/03 10:53

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

    >BCテーブルのB_id列を3つに増やすか、3つ組の関連テーブルを増やして同様のことをすればよいかと思います。
    >2つの場合、3つの場合はUNIONで結合する形になります。

    2つと3つの場合で別々にテーブルを持つ必要があるということなんですね。
    一緒にまとめたかったですが、それが最適ならそのようにする必要がありそうですね。

    >ご質問の内容は抽象化されており、A_id、B_id、C_idの関連がわかりませんので、実ケースでのよりよいデータの持ち方はすぐには思いつきませんでした。

    かなり専門的なことをしているので抽象化したのですが返ってわかりにくいでしたかね?
    すいません。

    化学構造のデータを扱っていまして

    A: 化学構造を含むライブラリ(データベース)名
    B: 化学構造自体のレコード
    AB: 複数のライブラリが同じ化学構造を含むことがあるためこのような形でリレーション
    C: Bの化学構造のうちの2あるいは3個を組み合わせた新規の化学構造
    BC: Cの新規の化学構造のために組み合わせたBの化学構造

    となっております。

    キャンセル

  • 2017/10/03 14:09

    追記ありがとうございました!

    なんとかできるんじゃないかなと思っていましたが
    やはり相当複雑なSQL文になりますね。
    テーブルの構造を見直してみます。

    なにはともあれありがとうございました!

    キャンセル

0

A: 化学構造を含むライブラリ(データベース)名
B: 化学構造自体のレコード
AB: 複数のライブラリが同じ化学構造を含むことがあるためこのような形でリレーション
C: Bの化学構造のうちの2あるいは3個を組み合わせた新規の化学構造
BC: Cの新規の化学構造のために組み合わせたBの化学構造

A:B=1:多
C:B=1:多
Aが含まれるCをカウントするということですね。
Aが含まれるBを抽出し、そのBが含まれるCをカウントするというイメージでしょうか。
効率化を考えなければ、単純にサブクエリーを利用するのが考えやすそうです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

  • 受付中

    SQLのひとつだけデータを消したい

    前提・実現したいこと AndroidでSQLiteをやっています。 deleteで条件で指定したそのレコードを削除できることはわかったのですが、ある列のある行のデータ一つだけを

  • 解決済

    [MySQL] 降順でLIMIT句をつけて取得したデータを逆の順で文字列結合したい

    MySQLのGROUP_CONCAT関数を使用して文字列を結合したいと考えています。 下の図のような取得をしたいのですが、試した限りではうまくいきませんでした... 何か良い方

  • 解決済

    mysql where句 複数テーブルで複数条件の摘出

    お世話になります。  mysqlを二日前から勉強し始めた超初心者です。  まったくの初心者なのですが、自力での解決が難しそうなので、教えて下さい。  <現状の問題点>

  • 解決済

    count関数について (PostgreSQL)

    現在SQLを学んでいます。 count関数について画像右の【販売履歴テーブル】でなぜエラーになるのか理解ができません。教本には 「【商品区分】もgroup byによってグループ

  • 解決済

    重複したデータを抽出させないSQL

    同一テーブル、または別テーブルに重複したデータが存在していれば、 それを抽出するCSVリストを作成したいと考えております。 ID SEI MEI 010 山田 太郎 02

  • 解決済

    SQLの結合について

    テーブルA |A1|A2| |1 |3| |2||4| テーブルB |B1||B2| |1||2| |3||NULL| 1.SQL SELECT A1,A2,B1,B2  FRO

  • 解決済

    GROUP BYで絞った結果をさらに絞る方法

    年月ごとの総数を求めたかったのですが、GROUP BYを利用すると,まとめた総数からさらに条件をつけて絞った条件総数がわからず困っています。 SELECT DATE_FORMA

  • 解決済

    [PostgreSQL][9.6.x]VIEW生成用SQLとVIEWのレコード数が相違する

     現象 あるテーブルAに複数のテーブル(B,C,D)をleft joinしてviewを作っているのですが、 view生成用SQLを実行すると結果として4レコード返ってくる(想定通り

同じタグがついた質問を見る

  • SQL

    2330questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    1037questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。