Is it possible to Join Queries based on Condition?

[raziel3]

Sometimes I find myself making one or two additional queries before I can get the join I need.

I have this sql called PROSOURCE that generates the WEIGHTID for me

SELECT COSTCENTRE.CCID, PROBRC.PDATE, PROBRC.QTYP, PROBRC.DIMENEXT, IIf([COSTCENTRE].[CCID]=11,[PROBRC].[DIMENEXT],[COSTCENTRE].[CCID]) AS WEIGHTID

FROM PROBRC

LEFT JOIN (FGMAS LEFT JOIN COSTCENTRE ON FGMAS.[CCID] = COSTCENTRE.[CCID]) ON PROBRC.FGMID = FGMAS.FGMID;

I have a WEIGHTS table with the fields WEIGHTID (can either be [DIMENEXT] or [CCID]), WEIGHT.

My question is, can I rewrite the current sql to JOIN WEIGHTID to the WEIGHTS table or do I have to make a whole new query using PROSOURCE and the WEIGHTS table?

eg LEFT JOIN IIf([COSTCENTRE].[CCID]=11,[PROBRC].[DIMENEXT],[COSTCENTRE].[CCID]) AS WEIGHTID ON WEIGHTS.WEIGHTID is this even possible?

 

[Pat Hartman]

Joins are not conditional. If you need to do this, you have a design problem with the schema. Perhaps we can help you to solve that instead. You probably shouldn't have two tables with the same type of data. One table with perhaps a column to indicate type could solve the problem.

 

[Ranman256]

or make a union query for all yr query conditions

 

[jdraw]

Can you show us your Relationships window?

 

[raziel3]

If you need to do this, you have a design problem with the schema.

I don't see how to differentiate it anymore but here's a sample of the data

 

[cheekybuddha]

With your dummy data, PRODUCTION.WEIGHTID = PRODUCTION.DIMENEXT where PRODUCTION.CCID = 11, so why not just join solely on WEIGHTS.WEIGHTID = PRODUCTION.WEIGHTID ?

 

[jdraw]

You have 3 tables in your query in #1.
CostCentre
ProBRC
FGMAS

But show WEIGHTS and PRODUCTION in #5.

It might be helpful if you showed all your tables and relationships, But David may have found what you need.

 

[raziel3]

With your dummy data, PRODUCTION.WEIGHTID = PRODUCTION.DIMENEXT where PRODUCTION.CCID = 11, so why not just join solely on WEIGHTS.WEIGHTID = PRODUCTION.WEIGHTID ?

On the Production query, WEIGHTSID has the IIF Statement. That's how the WEIGHTSID is being generated on the Production Query.

 

[plog]

You can JOIN the WEIGHTS table to PRODUCTION twice in the same query using an alias.

...LEFT JOIN WEIGHTS AS W1 ON W1.WEIGHTID = PRODUCTION.CCID
LEFT JOIN WEIGHTS AS W2 ON W2.WEIGHTID = PRODUCTION.WEIGHTID

Then in the SELECT (Or whatever other clause you need the logic) you use an IIF to pull from the correct instance of the WEIGHTS table (W1 or W2).

 

[raziel3]

You have 3 tables in your query in #1.
CostCentre
ProBRC
FGMAS

But show WEIGHTS and PRODUCTION in #5.

It might be helpful if you showed all your tables and relationships, But David may have found what you need.

I'm just pulled out the relevant data because I'm dealing with a huge, I mean HUGE, dataset. The main tables are PROBRC and WEIGHTS.

 

[cheekybuddha]

But David may have found what you need.

The more I read this thread, the less I understand

I think Pat's probably right that there is an issue with table design.

@raziel3, please post a screenshot of your relationships window.

 

[raziel3]

You can JOIN the WEIGHTS table to PRODUCTION twice in the same query using an alias.

...LEFT JOIN WEIGHTS AS W1 ON W1.WEIGHTID = PRODUCTION.CCID
LEFT JOIN WEIGHTS AS W2 ON W2.WEIGHTID = PRODUCTION.WEIGHTID

Then in the SELECT (Or whatever other clause you need the logic) you use an IIF to pull from the correct instance of the WEIGHTS table (W1 or W2).

Okay, let's take a step back. Production shown in #5 is not the table it's a query and the field WEIGHTID is generated by the IIF Statement. The Production Table doesn't have a WEIGHTID Field.

Joining on PRODUCTION.WEIGHTID means that I need to make a query (qryPRO) with the Production Table and then Join qryPRO to WEIGHTS which I was trying to avoid.

 

[plog]

My method holds. You only need one query in which you link to both possibilities then use logic to determine which of the 2 to use.

 

[raziel3]

My method holds. You only need one query in which you link to both possibilities then use logic to determine which of the 2 to use.

Ok I'll try it and update everyone. But yes thinking about it, makes sense.

 

[raziel3]

You can JOIN the WEIGHTS table to PRODUCTION twice in the same query using an alias.

...LEFT JOIN WEIGHTS AS W1 ON W1.WEIGHTID = PRODUCTION.CCID
LEFT JOIN WEIGHTS AS W2 ON W2.WEIGHTID = PRODUCTION.WEIGHTID

Then in the SELECT (Or whatever other clause you need the logic) you use an IIF to pull from the correct instance of the WEIGHTS table (W1 or W2).

@plog your method worked perfectly. I brought in the WEIGHTS Table twice and Joined like this