SQL Query issue involving multiple JOINS

Hello Folks

Looking for an SQL statement, or should I say an addition to an sql statement I already have working... note I am using Coldfusion MX7 with an Access database

<cfquery name="GetCompanys" datasource="#REQUEST.DS#">
SELECT COMPANY.*, TOWN.TOWNNAME, COUNTY.CountyName
FROM Company LEFT JOIN (Town LEFT JOIN County ON Town.CountyID = County.CountyID) ON Company.TownID = Town.TownID WHERE 0=0

Ignore the where 0=0 bit as I have loads of <CFIF statem,ents after for using this query as a search results generator.

That is by the by.

I am looking to incorporate ANOTHER JOIN into this statement.. as you see the current QUERY is a list of company details, which links to TOWN for town details... and TOWN links to COUNTY, however I want to bring another table into the query CONTACTS

Contacts has a field CompanyID and then an individuals information... there can be more than one contact per company so effectivly including all the above I want another JOIN (not inner as I want NULL matches returned also) ON Company.CompanyID = Contacts.CompanyID

If you wanna rewrite the whole thing, be my guest

Assistance greatly welcome.

ThanksStart Free Trial