When using this SQL query with a Access DB I get the "join expression not 
supported" error message.

SELECT * FROM (sfAttributeDetail INNER JOIN sfAttributes ON 
sfAttributeDetail.attrdtAttributeId = sfAttributes.attrID) INNER JOIN 
sfProducts ON sfAttributes.attrID = '" & objProdRS("prodAttNum")& "'"

I need to be able to pull the attribute information from the 
AttributeDetail table based on the attributeID equaling the attributeID in 
the main attribute table which is based on the prodAttNum in the Products 
database.

When I don't use objProdRS("prodAttNum") and instead say 
sfProducts.prodAttNum I don't get the error message, but I also do not get 
the correct results. It just pulls the first information from the 
database. I need the page to pull the attributeID based on the the 
prodAttNum attached to the product the user has selected. Does any of this 
make sense? Can anyone help me?

Thanks in advance
Erika Jones

If you want to apply a criteria to a resultset (eg values in this column
must equal x) then you use a WHERE clause.

A JOIN clause is used to join two *tables* (or views) together. You can't
JOIN a table to a criteria (which is what you are doing).

What you probably want is something like:

' DO NOT USE SELECT *!!!

SELECT
    Field1,
    Field2,
    Field3
FROM
(
        sfAttributeDetail AS a
    INNER JOIN
        sfAttributes AS b
    ON
        a.attrdtAttributeId = b.attrID
)
INNER JOIN
    sfProducts AS c
ON
    b.attrID = c.prodAttNum
WHERE
   b.attrID = '" & objProdRS("prodAttNum")& "'"

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Erika Jones" <ejones@n...>
Subject: [access_asp] join expression not supported error


: When using this SQL query with a Access DB I get the "join expression not
: supported" error message.
:
: SELECT * FROM (sfAttributeDetail INNER JOIN sfAttributes ON
: sfAttributeDetail.attrdtAttributeId = sfAttributes.attrID) INNER JOIN
: sfProducts ON sfAttributes.attrID = '" & objProdRS("prodAttNum")& "'"
:
: I need to be able to pull the attribute information from the
: AttributeDetail table based on the attributeID equaling the attributeID in
: the main attribute table which is based on the prodAttNum in the Products
: database.
:
: When I don't use objProdRS("prodAttNum") and instead say
: sfProducts.prodAttNum I don't get the error message, but I also do not get
: the correct results. It just pulls the first information from the
: database. I need the page to pull the attributeID based on the the
: prodAttNum attached to the product the user has selected. Does any of this
: make sense? Can anyone help me?
: