Conditional JOIN

Question

I'm wondering if it's possible to accomplish this in MS Access 2007:

A client gave me several tables, and they asked me for some queries. One of them has to get a field value from a table, depending on the value of a field of each record. This means, depending on the region, it has to look at one table, a second, or a third one.

So, I was wondering if I could do something like this:

SELECT
    table2.some_value
FROM
    table1
INNER JOIN table2
    ON CASE table1.SOME_VALUE THEN table3.id = table2.some_id ELSE
         CASE table1.SOME_VALUE THEN table4.id = table2.some_id ELSE 
              table5.id = table2.some_id END END

Is it clear? IF not, just ask and I'll answer your doubts.

EDIT:

I think I was not clear enough. I have a several joins in my query, but I have this last one, in which its ON statement will be different, depending on the data. For example:

I have a record in a table that has a State field, with three possibilities: CA, TX, FL.

If the value is CA, the ON statement of that JOIN should be CA_Standard_table.field = myTable.field.

If it's TX, the ON statement of that JOIN should be TX_Standard_table.field = myTable.field

And the same logic goes for FL.

How can I accomplish that?

EDIT 2:

Here is the query code, the last JOIN is the one that matters for this. The three possibilities of tables to join with in the ON statement are:

• EU_Accepted_Standards
• CA_Accepted_Standards
• NZ_Accepted_Standards

It will decide for one of them, depending of which of the following fields are checked:

• CAStandard: it should take CA_Accepted_Standards.
• EUSelStandard:it should take EU_Accepted_Standards.
• NZ_Accepted_Standards: it should take NZ_Accepted_Standards

Query

SELECT 

Projects.COMPAS_ID, 
Projects.[Opportunity Name], 
IIf([VolCap]=True,1) AS [Volume Cap], 
IIf([DelGuarantee]=True,1) AS [Delivery Guarantee], 
Projects.Tech_Level_Name, 
Counterparty.CPExpertise, 
Counterparty.CPFinStrength, 
Geographic_Location.Country_RiskLevel, 
Project_Stage_Risk.ProStaRiskLevel, 
Counterparty.CPExperience, 
Projects.Country_Name, 
IIf([EU ETS]=True,1) AS EU, 
IIf([CA ETS]=True,1) AS CA, 
IIf([NZ ETS]=True,1) AS NZ,
IIf([Australia ETS]=True,1) AS Australia, 
IIf([CAProjectType] is not null, CA_Accepted_Projects.CAPTRiskLevel, 
         IIf([EUSelProjType] is not null, EU_ETS_Standards.EUPTRiskLevel,
               IIf([NZSelProjType] is not null, NZ_Accepted_Projects.NZPTRiskLevel))) as [Risk Level],
IIf([CAStandard] is not null, CA_Accepted_Standards.CAStanRiskLevel, 
         IIf([EUSelStandard] is not null, EU_Accepted_Standards.EUStanRiskLevel,
               IIf([NZSelStandard] is not null, NZ_Accepted_Standards.NZStanRiskLevel))) as [Standard Risk]




FROM 

Project_Stage_Risk 

INNER JOIN (((((((((Counterparty 

INNER JOIN Projects 
             ON Counterparty.CPID = Projects.[Counter Party]) 

INNER JOIN Geographic_Location 
             ON Projects.Country_Name = Geographic_Location.Country_Name) 

left JOIN CA_Accepted_Projects 
             ON Projects.CAProjectType = CA_Accepted_Projects.CA_ProjectTypes) 

left JOIN NZ_Accepted_Projects 
             ON Projects.NZSelProjType = NZ_Accepted_Projects.NZ_StandardID) 

left JOIN EU_ETS_Standards
             ON Projects.EUSelProjType = EU_ETS_Standards.EU_StandardID) 

left JOIN CA_Accepted_Standards 
             ON Projects.CAStandard = CA_Accepted_Standards.ID) 

left JOIN NZ_Accepted_Standards
             ON Projects.NZSelStandard = NZ_Accepted_Standards.ID) 

left JOIN EU_Accepted_Standards
             ON Projects.EUSelStandard = EU_Accepted_Standards.ID)

left join Emissions_Trading_Systems
             ON Emissions_Trading_Systems.ETS = EU_Accepted_Standards.ETS)

ON Project_Stage_Risk.ProStaID = Projects.[Project Stage];

Answer 1

You could create a UNION query that unions together the three tables you want to conditionally join to, including a "Some_Value" column that will contain the item on which you want to join. Essentially, for each table you include in the UNION, set the value of the "Some_Value" column to a value you can use in a where clause to differentiate things. Then create an overall query that joins (in your example, table2) to the union query and use a WHERE clause to limit the records to the ones you need. I have done similar things myself on projects in the past with great success.

Answer 2

Join all five tables together, and use that CASE expression inside the SELECT clause to choose the appropriate field from all tables.

SELECT
    CASE table1.some_value 
         WHEN 'a' THEN table2.some_value
         WHEN 'b' THEN table3.some_value
         WHEN 'c' THEN table4.some_value
         WHEN 'd' THEN table5.some_value
    END

Answer 3

Use UNION of separate SELECT statement if you want distinct values. Use UNION ALL if you want to allow duplicates. UNION ALL is faster.

SELECT [field list] FROM myTable
INNER JOIN CA_Standard_table
ON CA_Standard_table.field = myTable.field
UNION
SELECT [field list] FROM myTable
INNER JOIN TX_Standard_table
ON TX_Standard_table.field = myTable.field;

Answer 4

Thanks for the answers. I know it was not well explained though, but in the end, I could solve this problem by writing a subquery.