CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005
RGebara
RGebara

Reputation: 11

Did not receive expected results when using LEFT JOIN?

I have the following SQL statement, and it returns only 2 records even though I have 300 employees. Does anyone see what I could be doing wrong?

SELECT     Employees.[Employee ID], 
Employees.Employee, 
Employees.[First Name], 
Employees.[Middle Name], 
Employees.[Last Name], 
Employees.Position,
[Work].[Work ID]
FROM  Employees LEFT JOIN
[Work] ON Employees.[Employee ID] = [Work].[Employee ID]
WHERE [Work].[Work Date] = '06-13-2012'

Upvotes: 0

Views: 71

Answers (4)

Jānis
Jānis

Reputation: 2266

Also try to check out [Work Date] column- if it also have time part (DateTime data type), then you should take that in account:

SELECT     
    Employees.[Employee ID], 
    Employees.Employee, 
    Employees.[First Name], 
    Employees.[Middle Name], 
    Employees.[Last Name], 
    Employees.Position,
    [Work].[Work ID]
FROM  Employees 
LEFT JOIN [Work] 
  ON Employees.[Employee ID] = [Work].[Employee ID]
WHERE Convert(Char(10), [Work].[Work Date], 120) = '2012-06-13' -- can find better way

Upvotes: 0

Guilherme Duarte
Guilherme Duarte

Reputation: 3441

Have you checked the date in the Where clause?

Try converting into a datetime or check if you don't have hours/minutes/seconds defined in the [Work].[Work Date] column data.

Try removing the join to see where is the problem:

SELECT [Work].[Employee ID], [Work].[Work ID]
FROM [Work]
WHERE [Work].[Work Date] = '06-13-2012'

How many records you have like this? Do all the Employee ID's match the ID's in Employees table?

If you need all the employees than you should do an LEFT OUTER JOIN:

SELECT     Employees.[Employee ID], 
Employees.Employee, 
Employees.[First Name], 
Employees.[Middle Name], 
Employees.[Last Name], 
Employees.Position,
[Work].[Work ID]
FROM  Employees LEFT OUTER JOIN
[Work] ON Employees.[Employee ID] = [Work].[Employee ID]
AND [Work].[Work Date] = '06-13-2012'

Is this what you want?

Upvotes: 0

to StackOverflow
to StackOverflow

Reputation: 124696

If you want all employees, perhaps you are looking for something like:

SELECT     Employees.[Employee ID],  ...
FROM  Employees LEFT JOIN 
[Work] ON Employees.[Employee ID] = [Work].[Employee ID] 
AND [Work].[Work Date] = '20120613'

Also note that you should use an unambiguous date format, e.g. yyyyMMdd

Upvotes: 2

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

When using outer join filter on outer table must be applied in ON clause, otherwise you effectively get inner join:

SELECT     
    Employees.[Employee ID], 
    Employees.Employee, 
    Employees.[First Name], 
    Employees.[Middle Name], 
    Employees.[Last Name], 
    Employees.Position,
    [Work].[Work ID]
FROM  Employees 
LEFT JOIN [Work] 
  ON Employees.[Employee ID] = [Work].[Employee ID]
 AND [Work].[Work Date] = '06-13-2012'

Upvotes: 3

Related Questions