SQL Query issue involving multiple JOINS
Posted on 2005-03-08
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.