inner join problem

[eurosytes]

Hi there,

I have a problem with the syntax for a multi-table inner join which I hope someone can help me with.

My SQL statement looks like this....
"SELECT orgs.documentref, mainser.name, ser.name, orgs.datein, serorgs.serid, orgs.title, orgs.shortdesc, orgs.webaddress, serorgs.webaddress, orgs.hits, orgs.webprefix, serorgs.webprefix, serorgs.title, serorgs.shortdesc, serorgs.hits, serorgs.datein, serorgs.serorgid, orgs.addressline1, orgs.addressline2, orgs.towncity, orgs.county, orgs.postcode, serorgs.supptelno, serorgs.suppemail, serorgser.serorgserid, serorgser.serorgid, serorgser.serid, serorgser.documentref, * FROM ((serorgser INNER JOIN orgs ON serorgser.documentref = orgs.documentref) INNER JOIN serorgs ON serorgser.serorgid = serorg.serorgid) ((INNER JOIN ser ON serorgser.serid = ser.serid) INNER JOIN mainser ON ser.mainserid = mainser.serid) WHERE (((orgs.active)=True)) ORDER BY mainser.name, serorgser.serid, serorgser.documentref, serorgs.title;

(sorry for the mouthfull of code!)
Basically I want to return all records from the SERORGSER table, and get related values from the ORGS table(1 record will be returned), SERORGS table(1 record will be returned), SER Table(1 record will be returned), & get values from the MAINSER table(1 record will be returned) based on the record found from the SER table.
We will exclude records where the value of the "orgs.active" field =True.

Hope this is clear !!
Many thanks for your help.
John

 

[PHV]

You may try this:
SELECT orgs.documentref, mainser.name, ser.name, orgs.datein, serorgs.serid, orgs.title, orgs.shortdesc, orgs.webaddress, serorgs.webaddress, orgs.hits, orgs.webprefix
, serorgs.webprefix, serorgs.title, serorgs.shortdesc, serorgs.hits, serorgs.datein, serorgs.serorgid, orgs.addressline1, orgs.addressline2, orgs.towncity, orgs.county, orgs.postcode
, serorgs.supptelno, serorgs.suppemail, serorgser.serorgserid, serorgser.serorgid, serorgser.serid, serorgser.documentref
FROM (((serorgser
INNER JOIN orgs ON serorgser.documentref = orgs.documentref)
INNER JOIN serorgs ON serorgser.serorgid = serorg.serorgid)
INNER JOIN ser ON serorgser.serid = ser.serid)
INNER JOIN mainser ON ser.mainserid = mainser.serid
WHERE orgs.active=True
ORDER BY mainser.name, serorgser.serid, serorgser.documentref, serorgs.title;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eurosytes]

Hi PH,
Thanks for your prompt response.
I'm afraid The syntax error remains...

Microsoft JET Database Engine error '80040e14'

Syntax error in JOIN operation.

/forum/esd-servresults-new.asp, line 198