sql - SQL中的菱形结构

Question

题
给定下表结构，其中 B和 C提供辅助信息，作为A之间的关联表和 D .

A <- B
^    |
|    v
C -> D 

是否有可能，对于给定的记录 a在 A , 检索 B 中的数据 和 C将其映射到给定值或一组值，在 D与 a 相关的?
示例场景
例如，考虑 a , 记录在 A , 代表一个公司和那个 D包含它可能购买或出售的产品以及 B和 C代表其购买和销售价格。所以对于给定的公司 a需要一份采购和销售价格的 list 是很合理的，这需要 B 的组合。 , C和 D .
我的第一个想法是加入这两个 B和 C一起组成一个复合表。然后使用复合表映射 A 中的记录致 D .类似以下内容可能就足够了:

# SQL Alchemy
prices = B.join(C, B.a_id==C.a_id and B.d_id==C.d_id)
A.join(prices, A.id==prices.a_id).join(D, D.id == prices.D_id)

# SQL (like)
SELECT B.A_ID OR C.A_ID as A_ID, B.PRICE AS B, C.PRICE AS C, B.D_ID OR C.D_ID 
as D_ID,
FROM B, C
WHERE B.D_ID = C.D_ID AND B.A_ID = C.A_ID

尽管我不确定这是解决此问题的最佳方法。
在 Gordon Linoff 的建议下，我将以下数据作为示例:

         A                               B             
  -----------------             ----------------------  
   ID |   Name                   A_ID | D_ID | Price   
  -----------------             ----------------------  
    1 | A Company                   1 |    1 |    10   
  -----------------                 1 |    3 |    30   
                                ----------------------

         C                                D                               
----------------------             -----------------                                  
 A_ID | D_ID | Price                 ID |   Name                                     
----------------------             -----------------                                  
    1 |    2 |   -20                  1 | Apples                                     
    1 |    3 |   -30                  2 | Bananas
----------------------                3 | Cucumbers
                                   -----------------

期望的结果如下

                         Result (A.ID=1)
             -------------------------------------
                   A     |  B  |  C  |    D
             -------------------------------------
               A Company |  10 |   - | Apples
               A Company |   - | -20 | Bananas
               A Company |  30 | -30 | Cucumbers
             -------------------------------------

最小工作(损坏)示例
在 Phillipxy 的建议下，这里是一个最小的工作 example (Click here @Phillipxy) .
笔记

我在上面使用了 SQL Alchemy 表示法，但我对纯 SQL 答案也很满意。

我看过相关问题，加入 D 似乎很常见与两者 C和 D表，但我没有发现任何然后将复合表连接回 A例如A -> (B + D + C) .更何况我还没有看到任何 zip B 的答案和 C在一起，这样一个人就可以加入 A至 D例如A -> (B + C) -> D .如果有更好的方法以所要求的方式组合所有四个表，我愿意接受建议。

点击 example按钮将带您到 S.E.带有模拟示例的查询环境(不幸的是，无法在临时表上设置外键)

Answer 1

假设 B 和 C 在 (a_id,d_id) 上都是唯一的:

SELECT B.PRICE AS B, C.PRICE AS C, D.NAME AS D
FROM D
LEFT JOIN B ON B.D_ID = D.ID
LEFT JOIN C ON C.D_ID = D.ID
WHERE B.PRICE IS NOT NULL OR C.PRICE IS NOT NULL