Need help with OLEDB query expression

1 Feb 2006 11:01 PM

Terry Olsen

I need to get information from 3 tables in an MDB file.

I need all the columns in the first table.
I need 2 columns in the 2nd table where it's primary key matches a
column in the first table.
I need several columns in the 3rd table where it's primary key matches a
column in the 2nd table.

I tried this:

SELECT calPackage.*,calShipment.m_shipDateTime,
calShipment.m_foreignKey00,calPkgAgent.m_residential,
calPkgAgent.m_StreetAddress,calPkgAgent.m_City,
calPkgAgent.m_StateProv,calPkgAgent.m_Country,
calPkgAgent.m_PostalCode,calPkgAgent.m_RoomFloor,
calPkgAgent.m_Department,calPkgAgent.m_Attention,
calPkgAgent.Sm_businessName
FROM calPackage
INNER JOIN calShipment
ON calShipment.m_primaryKey=calPackage.m_foreignKey
INNER JOIN calPkgAgent
ON calPkgAgent.m_primaryKey=calShipment.m_foreignKey00

but when I tried to execute the command, I get:

Syntax error (missing operator) in query expression
'calShipment.m_primaryKey=calPackage.m_foreignKey INNER JOIN calPkgAgent
ON calPkgAgent.m_primaryKey=calShipment.m_foreignKey00'.

I'm looking for any help I can get. Thanks!

*** Sent via Developersdex http://www.developersdex.com ***

1 Feb 2006 11:52 PM

Saber

I think you'll get your answer by posting your question here:
microsoft.public.access.queries

Show quoteHide quote

"Terry Olsen" <tolse***@hotmail.com> wrote in message
news:ukm0BO4JGHA.2912@tk2msftngp13.phx.gbl...
>I need to get information from 3 tables in an MDB file.
>
> I need all the columns in the first table.
> I need 2 columns in the 2nd table where it's primary key matches a
> column in the first table.
> I need several columns in the 3rd table where it's primary key matches a
> column in the 2nd table.
>
> I tried this:
>
> SELECT calPackage.*,calShipment.m_shipDateTime,
> calShipment.m_foreignKey00,calPkgAgent.m_residential,
> calPkgAgent.m_StreetAddress,calPkgAgent.m_City,
> calPkgAgent.m_StateProv,calPkgAgent.m_Country,
> calPkgAgent.m_PostalCode,calPkgAgent.m_RoomFloor,
> calPkgAgent.m_Department,calPkgAgent.m_Attention,
> calPkgAgent.Sm_businessName
> FROM calPackage
> INNER JOIN calShipment
> ON calShipment.m_primaryKey=calPackage.m_foreignKey
> INNER JOIN calPkgAgent
> ON calPkgAgent.m_primaryKey=calShipment.m_foreignKey00
>
> but when I tried to execute the command, I get:
>
> Syntax error (missing operator) in query expression
> 'calShipment.m_primaryKey=calPackage.m_foreignKey INNER JOIN calPkgAgent
> ON calPkgAgent.m_primaryKey=calShipment.m_foreignKey00'.
>
> I'm looking for any help I can get. Thanks!
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

2 Feb 2006 12:30 AM

George Shubin

You need to put the first join within a set of parenthesis:

SELECT calPackage.*,calShipment.m_shipDateTime,
calShipment.m_foreignKey00,calPkgAgent.m_residential,
calPkgAgent.m_StreetAddress,calPkgAgent.m_City,
calPkgAgent.m_StateProv,calPkgAgent.m_Country,
calPkgAgent.m_PostalCode,calPkgAgent.m_RoomFloor,
calPkgAgent.m_Department,calPkgAgent.m_Attention,
calPkgAgent.Sm_businessName
FROM
(calPackage
INNER JOIN calShipment
ON calShipment.m_primaryKey=calPackage.m_foreignKey)

INNER JOIN calPkgAgent
ON calPkgAgent.m_primaryKey=calShipment.m_foreignKey00

--
------------------------------------------------------------------------
George Shubin       Custom Software Development
dX Software Systems          Database Applications
Ph: 503-981-6806                     Fax: 503-982-0120
www.dxonline.com              geo***@dxonline.com
------------------------------------------------------------------------

"The sum which two married people owe to one another defies calculation. It
is an infinite debt, which can only be discharged through all eternity." --
Goethe

Show quoteHide quote

"Terry Olsen" <tolse***@hotmail.com> wrote in message
news:ukm0BO4JGHA.2912@tk2msftngp13.phx.gbl...
>I need to get information from 3 tables in an MDB file.
>
> I need all the columns in the first table.
> I need 2 columns in the 2nd table where it's primary key matches a
> column in the first table.
> I need several columns in the 3rd table where it's primary key matches a
> column in the 2nd table.
>
> I tried this:
>
> SELECT calPackage.*,calShipment.m_shipDateTime,
> calShipment.m_foreignKey00,calPkgAgent.m_residential,
> calPkgAgent.m_StreetAddress,calPkgAgent.m_City,
> calPkgAgent.m_StateProv,calPkgAgent.m_Country,
> calPkgAgent.m_PostalCode,calPkgAgent.m_RoomFloor,
> calPkgAgent.m_Department,calPkgAgent.m_Attention,
> calPkgAgent.Sm_businessName
> FROM calPackage
> INNER JOIN calShipment
> ON calShipment.m_primaryKey=calPackage.m_foreignKey
> INNER JOIN calPkgAgent
> ON calPkgAgent.m_primaryKey=calShipment.m_foreignKey00
>
> but when I tried to execute the command, I get:
>
> Syntax error (missing operator) in query expression
> 'calShipment.m_primaryKey=calPackage.m_foreignKey INNER JOIN calPkgAgent
> ON calPkgAgent.m_primaryKey=calShipment.m_foreignKey00'.
>
> I'm looking for any help I can get. Thanks!
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***