SQL Query from "Triangular Relation" of Tables

Question

I am having a hard time to get a - seemingly simple - right in SQL. I am using MS Access but I guess it doesn't really matter here. My data structure looks like this:

So tblA has the "Products", tblB the "Components" and tblC the "Modules". It's just an example obviously. tblA ("Product") has m:n relation to tblB ("Component"), i.e. a product consists of some components and components can belong to many products. tblB ("Component") has m:n relation to tblC ("Modules"), i.e. a component consists of modules and modules can belong to many components. Now the twist is, that I also have a m:n relation between tblA and tblC, i.e. a mapping of products and modules. This is due to the fact that the "configuration" of a component can be different depending on the product for which is it. So for "Product 1" "Component 2" needs to consist of "Module 1" and "Module 2" while for "Product 2" "Component 2" is just "Module 2".

I want a query with a result like this

|Product|Component|Module|
|-------|---------|------|
|Prod1  |C1       |      |
|Prod1  |C2       |      |
|Prod1  |C2       |M1    |
|Prod1  |C2       |M2    |
|Prod2  |C2       |      |
|Prod2  |C2       |M2    |
|Prod2  |C3       |      |
|Prod2  |C3       |M3    |

where I have "Prod1" and "Prod2" in tblA; "C1", "C2", "C3" in tblB and "M1","M2","M3" in tblC. Relations are

So essentially I want a table with Product, Component, Module that holds all the combinations Product+

Now with a SQL Query

SELECT tblA.Product,
       tblB.Component,
       tblC.Module
FROM tblC
INNER JOIN ((tblA
             INNER JOIN (tblB
                         INNER JOIN mapAB ON tblB.[Component] = mapAB.[Component]) ON tblA.[Product] = mapAB.[Product])
            INNER JOIN mapAC ON tblA.Product = mapAC.Product) ON tblC.Module = mapAC.Module;

I get

which is understandable but not what I want. I have tried to do first a join of tblA and tblB and to combine this with a LEFT JOIN (or RIGHT JOIN) with a join of tblB and tblC but this gives "unsupported join operation" errors.

Any ideas on how to approach this problem?

user_10901808 · Answer

Looking at your relationships, you haven't used your primary keys to make the one to many relationships, but the data fields.

In this case, this query should work for you:

SELECT tblA.Product, mapAC.Module, MapAB.Component
FROM (tblA LEFT JOIN mapAC ON tblA.Product = mapAC.Product) LEFT JOIN MapAB ON tblA.Product = MapAB.Product;

All the other tables are in this case just informative since you already have the data available in the tables MapAB and MapAC.

I would advise using the primary keys in your relationships.

Update: Your query can follow three routes to get the data you need:

tblA -> mapAB + mapAC
tblA -> mapAB -> tblB -> mapBC -> tblC
tblA -> mapAC -> tblC -> mapBC -> tblB

Depending on which data route you want to follow your query will look differently.

Route 2 Query:

SELECT tblA.Product, MapAB.Component, mapBC.Module
FROM (((tblA LEFT JOIN MapAB ON tblA.Product = MapAB.Product) LEFT JOIN tblB ON MapAB.Component = tblB.Component) LEFT JOIN mapBC ON tblB.Component = mapBC.Component) LEFT JOIN tblC ON mapBC.Module = tblC.Module;

Route 3 Query:

SELECT tblA.Product, mapAC.Module, mapBC.Component
FROM (((tblA LEFT JOIN mapAC ON tblA.Product = mapAC.Product) LEFT JOIN tblC ON mapAC.Module = tblC.Module) LEFT JOIN mapBC ON tblC.Module = mapBC.Module) LEFT JOIN tblB ON mapBC.Component = tblB.Component;

user_392772 · Answer

Ok, based on jbud suggestions I was able to build up the desired result like this:

qryProdModComp = SELECT tblA.Product, mapBC.Component, mapAC.Module
FROM (tblA LEFT JOIN mapAC ON tblA.Product = mapAC.Product) LEFT JOIN mapBC ON mapAC.Module = mapBC.Module;

already almost gives what I want

Notice however, that the line with Prod1 and C1 is missing (for which no Mx is directly linked). To get this I added

qrySingleC = SELECT tblB.Component AS Component
FROM   tblB
       LEFT JOIN mapBC
       ON tblB.Component = mapBC.Component
WHERE  mapBC.Component Is Null
UNION
SELECT mapBC.Component
FROM   tblB
       RIGHT JOIN mapBC
       ON tblB.Component = mapBC.Component
WHERE  tblB.Component Is Null

qryProdCompOnly = SELECT tblA.Product, mapAB.Component, NULL AS Module
FROM tblA INNER JOIN ( qrySingleC INNER JOIN mapAB ON qrySingleC.Component = mapAB.Component  ) ON tblA.Product = mapAB.Product;

qryOverall = SELECT Product,Component,Module FROM qryProdCompMod UNION SELECT Product,Component,Module FROM qryProdCompOnly;

which gives

Getting to the exact same result as requested in my original post actually looks still a bit cleaner

qryProdCompOnly2 = SELECT tblA.Product, mapAB.Component, NULL AS Module
FROM tblA INNER JOIN mapAB ON tblA.Product = mapAB.Product;

qryOverall = SELECT Product,Component,Module FROM qryProdCompMod UNION SELECT Product,Component,Module FROM qryProdCompOnly2;

gives

SQL Query from "Triangular Relation" of Tables

2 Answers