returning results of employee table even if

• Andy Connock

  Newbie

  Points: 5

  More actions

  June 26, 2008 at 2:03 pm

  #187637

  i have two tables:

  an employee table and a transaction table

  both have common fields: empnum

  as an employee creates a transaction, a new row is created in the transaction table

  i need to return a query that returns:

  ALL employees and their transactions:

  (1) employees that created transactions; AND

  (2) employees that have not created transactions

  so far, all i'm seeing are those that created transactions.

  here's my SELECT statement. the table Prevention Response is the transaction table i refer to.

  SELECT TOP 100 PERCENT dbo.[Prevention Response].ID, dbo.[Prevention Response].theSubject, dbo.[Prevention Response].Date,

  dbo.[Prevention Response].theAction, dbo.[Prevention Response].theType, dbo.[Prevention Response].Employee,

  dbo.[Prevention Response].Intervention, dbo.v_EmployeePrevResp.TheName AS Fullname, dbo.v_EmployeePrevResp.Department,

  MONTH(dbo.[Prevention Response].Date) AS TheMonth

  FROM dbo.[Prevention Response] RIGHT OUTER JOIN

  dbo.v_EmployeePrevResp ON dbo.[Prevention Response].Employee = dbo.v_EmployeePrevResp.EmpNum LEFT OUTER JOIN

  dbo.[Prevention Response] AS [Prevention Response_1] ON dbo.v_EmployeePrevResp.EmpNum = [Prevention Response_1].Employee

  WHERE (dbo.[Prevention Response].Date > CONVERT(DATETIME, '2007-12-31 00:00:00', 102))

  ORDER BY dbo.[Prevention Response].Date

  appreciate any help.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 26, 2008 at 2:19 pm

  #834970

  The problem is the Where clause:

  "WHERE (dbo.[Prevention Response].Date > CONVERT(DATETIME, '2007-12-31 00:00:00', 102))"

  If an employee doesn't have any entries in Prevention Response, the Date column will be null.

  If you move that criterion up to your Join clause, out of the Where clause, it should work the way you want.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon