If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Need help creating outer join on multiple tables

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Posts: 2
Need help creating outer join on multiple tables

I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.

I'm a SQL code novice so any help would be greatly appreciated!

Here is my existing SQL query (without the new outer join table):

SELECT
Lead_.Country, Lead_.Company_Name, Employee.Full_Name, Lead_.Rn_Create_Date, Lead_.Marketing_Project_Name, Employee_1.Full_Name, Lead_.Comments

FROM
ProductionED.dbo.Employee Employee, ProductionED.dbo.Employee Employee_1, ProductionED.dbo.Lead_ Lead_

WHERE
Employee.Employee_Id = Lead_.Account_Manager_Id AND Employee_1.Employee_Id = Lead_.Created_By_Employee_Id AND ((Lead_.Market_Segment='new'))
Reply With Quote
  #2 (permalink)  
Old
Annie's Dog Walker
 
Join Date: Nov 2004
Location: on the wrong server
Posts: 8,516
don't know exactly what you want but i think it is something like this...

Code:
SELECT Lead_.Country, 
Lead.Company_Name, 
Employee.Full_Name, 
Lead.Rn_Create_Date, 
Lead.Marketing_Project_Name, 
Employee_1.Full_Name, 
Lead.Comments
FROM ProductionED.dbo.Employee Employee
LEFT OUTER JOIN 
	(SELECT Company_Name,Country,Account_Manager_Id,
	 Rn_Create_Date,Marketing_Project_Name,Comments
	 FROM ProductionED.dbo.Lead_
	 WHERE Lead_.Market_Segment='new')  Lead
ON Employee.Employee_Id = Lead.Account_Manager_Id
LEFT OUTER JOIN ProductionED.dbo.Employee Employee_1
ON Lead.Created_By_Employee_Id = Employee_1.Employee_Id
Your join syntax went out of vogue in 1992.
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Posts: 2
Hi Sean, Thanks for the fast reply. I know the original SQL code is probably from the stone age - Microsoft Query produced this based on the "graphical interface".

Sorry I wasn't clear in the purpose: What I want to do is add another table to the already existing query - however this table must be added through an outer join. Here's the table added (ProductionED.dbo.Product_Group), but Microsoft Query can only make an inner join, which eliminates a lost of records. How do I change the query to make "ProductionED.dbo.Product_Group Product_Group" an outer join?

SELECT Lead_.Country, Lead_.Company_Name, Employee.Full_Name, Lead_.Rn_Create_Date, Lead_.Marketing_Project_Name, Employee_1.Full_Name, Lead_.Comments, Product_Group.Product_Group_Name
FROM ProductionED.dbo.Employee Employee, ProductionED.dbo.Employee Employee_1, ProductionED.dbo.Lead_ Lead_, ProductionED.dbo.Product_Group Product_Group
WHERE Employee.Employee_Id = Lead_.Account_Manager_Id AND Employee_1.Employee_Id = Lead_.Created_By_Employee_Id AND Product_Group.Product_Group_Id = Lead_.Product_Group_Id AND ((Lead_.Market_Segment='new'))

Thanks again for helping an absolute SQL illiterate...

/johan
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 22
Johan,

If you want to combine a left outer join with the query that you have listed, you're going to need to structure your initial table joins in a certain way.

Small example:

Original:
SELECT a.col1, b.col1
FROM tblA a, tblB b
WHERE a.col1 = 'blah'

Using outer join:
SELECT a.col1, b.col1, c.col1
FROM tblA a, tblB b
LEFT OUTER JOIN tblC c
ON b.value = c.value
WHERE a.col1 = 'blah'

The thing is, using the ON comparison with the combination of join syntax, you will need to compare a value from your outer join table with a value from the LAST table you list in the initial FROM joins.

You cannot do this:
SELECT a.col1, b.col1, c.col1
FROM tblA a, tblB b
LEFT OUTER JOIN tblC c
ON a.value = c.value -- Notice the order of the table joins above
WHERE a.col1 = 'blah'

Hope that helps.
__________________
www.socialec.com - Social Engineering/NLP
Reply With Quote
  #5 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Code:
SELECT Lead_.Country
     , Lead_.Company_Name
     , Employee.Full_Name
     , Lead_.Rn_Create_Date
     , Lead_.Marketing_Project_Name
     , Employee_1.Full_Name
     , Lead_.Comments
     , Product_Group.Product_Group_Name
  FROM ProductionED.dbo.Lead_      AS Lead_
INNER
  JOIN ProductionED.dbo.Employee   AS Employee
    ON Employee.Employee_Id = Lead_.Account_Manager_Id 
INNER
  JOIN ProductionED.dbo.Employee   AS Employee_1
    ON Employee_1.Employee_Id = Lead_.Created_By_Employee_Id
LEFT OUTER
  JOIN ProductionED.dbo.Product_Group AS Product_Group
    ON Product_Group.Product_Group_Id = Lead_.Product_Group_Id  
 WHERE Lead_.Market_Segment = 'new'
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 22
Annnnnd that would be the best way to do it.
__________________
www.socialec.com - Social Engineering/NLP
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On