ON clause with a reference to more than 2 tables

[rmuller]

I use this query in a application. With mySQL it works fine (and fast). Access does NOT accept an "ON clause" with a reference to more than 2 tables, so that is my problem. What alternative can i use with Access???

example:

SELECT ...
    FROM ((((Products AS P
    INNER JOIN ProductNames AS PN ON P.ProductId=PN.ProductId)
    INNER JOIN ClassProducts AS CP ON P.ProductId=CP.ProductId)
    INNER JOIN ProductClasses AS C ON CP.ClassId=C.ClassId)
    INNER JOIN ClassFeatures AS CF ON C.FeatureClassId=CF.ClassId)
    LEFT JOIN ProductFValues AS PV ON P.ProductId=PV.ProductId AND CF.ClassFeatureId=PV.ClassFeatureId 
    WHERE P.ProductCode=? AND PN.LocaleId=1

Thanks!

Ronald

 

[MichaelRed]

Hmmmmmmmmmmmmmm,

No complete answer (at least from me). Whenever Ms. A. won't let me write a query 'just the way I want, I fall back to the 'layered' effect. Make the 'parts' I can do as seperate queries and then layer another on top of those. I have (on RARE occassions) had to resort to several of these layers to achieve a final result. When layering, you will generally experience noticable slow-down in the response, but the results are at least possible.

In your exanple, I '[b[THINK[/b] the problem is attempting the left join. Try doing all of the inner joins in one query and then layering the left join as a seperate one.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[rmuller]

tnx michael,

i also tried the layered approach, but ..
the query is VERY slow, so slow that it is not acceptable (for my client). The mySQL query is very fast (this query is against a 30+MB database).

Ronald