Problem with Join in SQL Statement

[UncleHuckleberry]

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, Sum(Total) As Total1, LRTime_Stamp, 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.

Any help would be appreciated.

"When the going gets wierd, the wierd turn pro" - R. Duke

 

[PHV]

Have a look at outer joins.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[UncleHuckleberry]

I've experimented with joins but the criteria I add in the WHERE statement filters the employees and doesn't show them all. I guess i'll have to apply the join after making the WHERE statement, but i'm unsure how to write this in SQL.

"When the going gets wierd, the wierd turn pro" - R. Duke

 

[JerryKlmns]

SELECT Employees.Country, Employees.Region, Employees.Company, Employees.FullName, Employees.EmpNo, qSel_All_Data_2.Total1, qSel_All_Data_2.LRTime_Stamp, qSel_All_Data_2.LRSupervisor_EmpNo, qSel_All_Data_2.LRSupervisor_Name
FROM Employees Left Join qSel_All_Data_2 On
(Employees.Country = qSel_All_Data_2.Country) AND
(Employees.Region = qSel_All_Data_2.Region) AND
(Employees.Company = qSel_All_Data_2.Company) AND
(Employees.Empno = qSel_All_Data_2.Empno)