Need help with nested JOIN

[Joe Needs Help]

Here is a query I can't get to run.

TestStep contains ALL the records I want.
TestStep_Parameter is only a link table
Parameter contains some records that match to the TestStep table, but not
all TestSteps contain a Parameter. Therefore I want an OUTER JOIN between
TestStep and TestStep_Parameter, but I can't get the query to run. It just
says "JOIN Expression not supported."

SELECT TestStep.ComponentID, TestStep.SortID, TestStep.Description,
TestStep.ExpectedResult, Parameter.Application, Parameter.Location,
Parameter.DataFieldName
FROM
Parameter INNER JOIN
(TestStep_Parameter RIGHT JOIN TestStep
ON TestStep_Parameter.TestStepID = TestStep.TestStepID)
ON Parameter.ParameterID = TestStep_Parameter.ParameterID
ORDER BY TestStep.ComponentID, TestStep.SortID;

 

[Michel Walsh]

Jet uses ( ) but they do *not* describe the order of execution, in a FROM
clause, so, that makes the joins you present as ambiguous.


Make a first query between TestStep_Parameter and TestStep, with the Right
Join

TestStep_Parameter RIGHT JOIN TestStep
ON TestStep_Parameter.TestStepID = TestStep.TestStepID


Save it. Make a second query, bring Parameter and the query you just save,
make you inner join.



Hoping it may help,
Vanderghast, Access MVP

 

[Joe Needs Help]

Wow... I can't believe () does not describe order. thank you very much for
your help, I used the method you described below and the solution worked
perfect.

Thank you.

Regards,
Joe

 

[Michael_Randall]

Hi Michel,

Do you have to solve this problem by using 2 seperate queries? Could you
also nest the joins in one query?

Thanks

 

[vanderghast]

You can use a cascade of query, as you do.

You can use a virtual table:

SELECT * FROM a INNER JOIN (SELECT * FROM b RIGHT JOIN c ON ... ) AS x
ON a.f1=x.g1


And, sometimes, as third option, if you don't have an ambiguous join, in
Jet, you can try directly to use joins. But unfortunately, in your case, it
seems you have an ambiguous join and Jet syntax cannot determine if the
inner join has to be executed before or after the outer join. If you use MS
SQL Server, you don't have that problem, though, since the parenthesis will
indicate which join has to be performed first. But unfortunately, with JET,
the parenthesis do not indicate play that role, when mixing joins.


Vanderghast, Access MVP