Results 1 to 6 of 6
  1. #1
    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'))

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,575
    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.

  3. #3
    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

  4. #4
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  6. #6
    Join Date
    Oct 2007
    Posts
    22
    Annnnnd that would be the best way to do it.
    www.socialec.com - Social Engineering/NLP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •