?
Solved

SQL Query issue involving multiple JOINS

Posted on 2005-03-08
7
Medium Priority
?
16,182 Views
Last Modified: 2013-12-24
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.

Thanks
0
Comment
Question by:mvwmail
7 Comments
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13489910
Not too sure if this would work like a charm, but worht a try, it's hard to tell without seeing your table and columns layed out.
I beleive all you need to do is add one more LEFT JOIN in the center of your SQL FROM.

Have a look..

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

How's that work?

Regards,
~trail
0
 
LVL 18

Expert Comment

by:Plucka
ID: 13492729
Hi mvwmail,

It's fairly simple if I understand what you want.

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

That's how it should be written, they way you have it I have no idea what that is, looks like a mess to me.

Regards
Plucka
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13492767
Hi mvwmail!

How about a revision of your query ???

:: Revised Query ::
SELECT COMPANY.*, TOWN.TOWNNAME, COUNTY.CountyName, CONTACTS.*
FROM Company
LEFT JOIN Contacts
  ON Contacts.CompanyID = Company.CompanyID
LEFT JOIN Town
  ON Town.TownID = Company.TownID
LEFT JOIN County
  ON County.CountyID = Town.CountyID

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 9

Assisted Solution

by:Jerry_Pang
Jerry_Pang earned 240 total points
ID: 13504178
which one is null?

i dont understand where the null is.

Contact.CompanyID?

hmmm..


SELECT COMPANY.*, TOWN.TOWNNAME, COUNTY.CountyName,
Contacts.Name
FROM
Contacts, Company LEFT JOIN
    (Town LEFT JOIN County ON Town.CountyID = County.CountyID)
    ON Company.TownID = Town.TownID
)
WHERE 0=0
AND Contacts.CompanyID = Company.CompanyID
OR Contacts.CompanyID = NULL

??? hmmm no need to make joins.  this will add all contactinformation

ps. dont use Company.* use Company.ID, Company.name, company.address and so on..

0
 
LVL 3

Author Comment

by:mvwmail
ID: 13506524
Pluka and Entrance... I get a message indicating that this JOIN is not supported

Same with Jerry, JOIN not supported

Trail blazer, got a syntax error with that one... but in the right direction I think

Thanks so far guys

Morgan
0
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 260 total points
ID: 13507307
mvwmail,

Check that the joins are right and the way you need them, I'm not sure exactly how your tables are setup or what the rest of your scripts are.
Here, try this query and see what happens, this may solve the syntax error you got back.

<CFQUERY NAME="GetCompanys" DATASOURCE="#REQUEST.DS#">
SELECT COMPANY.*,
            TOWN.*,
            COUNTY.*,
            CONTACTS.*

FROM COMPANY  LEFT JOIN
    (TOWN           LEFT JOIN
    (CONTACTS    LEFT JOIN COMPANY ON COMPANY.CompanyID  = CONTACTS.CompanyID)
                          ON TOWN.CountyID           = COUNTY.CountyID)
                               ON COMPANY.TownID        = TOWN.TownID

WHERE 0=0
<!---Your scripts--->

</CFQUERY>

Hope this works for ya,
Regards,
~trail
0
 
LVL 3

Author Comment

by:mvwmail
ID: 13603571
Got what I wanted in the end a different way.. thanks for all submission, thouhg a couple had no chance of working. Did try your script anyway trailblazzyr55.. still got a syntax error..

Points split
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Integration Management Part 2
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question