###前提
- 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文を以下のように考えました。
sql
1SELECT COUNT(DISTINCT(C_id)) FROM BC WHERE B_id in ( 2 SELECT B_id FROM AB WHERE A_id = 1 3)
しかし、この自分で考えた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の化学構造
となっております。
回答3件
あなたの回答
tips
プレビュー