Diamond structures in SQL

user_958580 0
Viewed 244

Question

Given the following table structure, where B and C provide auxiliary information and serve as the association tables between A and D.

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

Is it possible, for a given record a in A, to retrieve the data in B and C that map it to a given value, or set of values, in D that are related to a ?

Example Scenario

As an example consider that a, a record in A, represents a company and that D contains the products it might buy or sell and that B and C represent the purchase and sales prices thereof. So for a given company a it would be quite reasonable to require a list of purchase and sales prices, which requires a combination of B, C and D.

My first thought is to join both B and C together into a compound table. Then use the compound table to map the record(s) in A to those in D. Something like the following might suffice :

# 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

Though I'm not certain this is the best way to go about this.

At the suggestion of Gordon Linoff I have included the following data as an example :

         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
                                   -----------------

with which the desired result would be as follows

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

Minimum Working (Broken) Example

At the suggestion of Phillipxy here is a minimal working example (Click here @Phillipxy).

Notes

  • I've used SQL Alchemy notation above but I'm happy with a pure SQL answer too.

  • I've looked at related questions and it seems common to join D with both C and D table but I haven't spotted any that then join the compound table back to A e.g. A -> (B + D + C). More so I haven't seen any answers that zip B and C together such that one can then join A to D e.g. A -> (B + C) -> D. If there is a better means of combining all four tables in the manner asked I'm open to suggestions.

  • Clicking the example button will take you to the S.E. query environment with a mock example (Unfortunately one is prevented from setting up foreign keys on temporary tables)

sqlalchemy join sql
3 Answers

assuming that B and C are each unique over (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
user_141267100

I found I can stitch the two tables, B and C, together as follows :

SELECT 
   (case when B.A_ID is not null
         then
             B.A_ID
         else
             C.A_ID
         end) A_ID,
   B.Price,
   C.Price,
   (case when B.D_ID is not null
         then
             B.D_ID
         else
             C.D_ID
         end) D_ID
FROM B FULL OUTER JOIN C ON B.A_ID = C.A_ID AND B.D_ID = C.D_ID;

combining the above with tables A and D gives one the following query :

SELECT A.Name, M.Sales, M.Purchase, D.Name
FROM A
JOIN (
SELECT 
   (case when B.A_ID is not null
        then
            B.A_ID
        else
            C.A_ID
            end) A_ID,
   B.Price as Sales,
   C.Price as Purchase,
   (case when B.D_ID is not null
        then
            B.D_ID
        else
            C.D_ID
            end) D_ID
   FROM B FULL OUTER JOIN C ON B.A_ID = C.A_ID AND B.D_ID = C.D_ID) M ON A.ID = M.A_ID
JOIN D ON D.ID = M.D_ID

This reads a little like the love child of Frankenstein's monster and a cast member of Jaws that never made it to the credits was encoded as ASCII; so I'll wait for a better suggestion before assigning the Tick.

user_9585800

I think your solution is fine, personally I would prefer COALESCE to shorten it a bit

SELECT A.Name, M.Sales, M.Purchase, D.Name
FROM A JOIN (
   SELECT 
   COALESCE(B.A_ID, C.A_ID) AS A_ID,      
   B.Price as Sales,
   C.Price as Purchase,
   COALESCE(B.D_ID, C.D_ID) AS D_ID
   FROM B FULL OUTER JOIN C ON B.A_ID = C.A_ID 
                           AND B.D_ID = C.D_ID) M ON A.ID = M.A_ID
JOIN D ON D.ID = M.D_ID

The approach of John M. Owen is possible too, just add second table

SELECT A.NAME AS A, B.PRICE AS B, C.PRICE AS C, D.NAME AS D 
FROM A
CROSS JOIN D
LEFT JOIN B ON B.D_ID = D.ID AND B.A_ID = A.ID
LEFT JOIN C ON C.D_ID = D.ID AND C.A_ID = A.ID
WHERE B.PRICE IS NOT NULL OR C.PRICE IS NOT NULL
user_49592210
Related