?
Solved

SQL Query issue involving multiple JOINS

Posted on 2005-03-08
7
Medium Priority
?
16,177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

752 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