Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: SQL Join and Sub Query problem

    I'm having problems writing the SQL to Join a query with a table to show all employees for a particular company.

    I have the following query so far:

    SELECT Country, Region, Company, FullName, EmpNo, LRTime_Stamp, Total, LRSupervisor_EmpNo, LRSupervisor_Name
    FROM qSel_All_Data_2
    WHERE (Country ='England') AND (Region = 'Yorkshire') AND (Company = 'MMM') AND (Type = 'Business')
    GROUP BY Country, Region, Company, FullName, LRTime_Stamp, LRSupervisor_EmpNo, LRSupervisor_Name;

    I then want to join this to a table called 'Employees' and show all of the records in the 'Employees' table regardless of whether they are in the 'qSel_All_Data_2' query or not.

    The 'Employees' table has the following fields:

    ID
    Country
    Region
    Company
    EmpNo
    Fullname

    And I would want to join on Country, Region, Company, Empno.

    At the moment when I try and join it only returns the records that satisfy the WHERE criteria. Do I need a subquery of some kind???

    Any help would be appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need an OUTER JOIN:

    SELECT e.id, ..., q.xxx, ...
    FROM employees e
    LEFT OUTER JOIN qSel_All_Data_2 q
    ON q.country = e.country AND ...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also - your group by clause appears a little superfluous
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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