
February 15th, 2006, 07:19 AM
|
|
Registered User
|
|
Join Date: Aug 2004
Posts: 10
Time spent in forums: 24 m 9 sec
Reputation Power: 0
|
|
LEFT & RIGHT JOIN problem
I have 3 tables "tblCategory", "tblSubCategory" and "tblJoinSub2Cat". I need all the records in tblCategory which match a certain criteria, along with each SubCategory associated to that category (associations are stored in tblJoinSub2Cat).
Heres my sql (which gives me a "Join expression not supported" error):
Code:
SELECT tblCategory.fieldName, tblSubCategory.fieldName
FROM tblSubCategory
INNER JOIN (tblJoinSub2Cat RIGHT JOIN tblCategory ON tblJoinSub2Cat.fieldName=tblCategory.fieldName)
ON tblSubCategory.fieldName=tblJoinSub2Cat.fieldName
WHERE (tblCategory.FieldName='"&Variable&"')
However, when I take the RIGHT JOIN out of the 'nest' I get no error, as shown below:
Code:
SELECT tblCategory.fieldName
FROM tblJoinSub2Cat
RIGHT JOIN tblCategory
ON tblJoinSub2Cat.fieldName=tblCategory.fieldName
WHERE tblCategory.fieldName='"&Variable&"'
Im using MS Access and ASP VBscript.
Any suggestions will be great appreciated...
Ads
|