which sql join query should i use?

Question

i'm a noob in using complex query.. so i'm a little bit confused around here..

here are the problem :

i had 2 table, the first one is

employee :

empID name  branchID   etc
 1    ab      1        ...
 2    abc     4        ...
 3    ad      4        ...

and the second table was

employeeAttendance :

empID   attDate     hourIn  hourOut  etc
  1    05-06-2013    12.00   14.00   ...
  1    05-07-2013    10.00   14.00   ...
  1    05-10-2013    09.00   12.00   ...
  2    05-06-2013    08.00   14.00   ...
  2    05-10-2013    08.00   10.00   ...
  3    05-09-2013    11.00   15.00   ...

and what i'm trying to accomplish is this view :

empID name   attDate     hourIn  hourOut  etc
  1    ab  05-06-2013    12.00   14.00   ...
  2    abc 05-06-2013    08.00   14.00   ...
  3    ad  05-06-2013    null    null    ...
  1    ab  05-07-2013    10.00   14.00   ...
  2    abc 05-07-2013    null    null    ...
  3    ad  05-07-2013    null    null    ...
  1    ab  05-09-2013    null    null    ...
  2    abc 05-09-2013    null    null    ...
  3    ad  05-09-2013    11.00   15.00   ...
  1    ab  05-10-2013    09.00   12.00   ...
  2    abc 05-10-2013    08.00   10.00   ...
  3    ad  05-10-2013    null    null    ...

i'm using sql server management studio 2008, it's funny, i felt this is so easy but i couldn't make it afterall, i have tried to use LEFT OUTER JOIN, RIGHT OUTER JOIN, INNER JOIN, and even CROSS JOIN, but none of them give me the result i want..

the one that nearly give me the answer was CROSS JOIN but the ID didn't match because CROSS JOIN didn't use ON clause.. and when i add WHERE, it automatically became INNER JOIN..

so did i miss something in here? sorry if this question is silly, and sorry for the bad english :)

Answer 1

 WITH DateList AS(
 SELECT DISTINCT E.EmpiD,E.Name,EA.AttDate FROM EmployeeAttendance EA
 CROSS JOIN Employee E )

 SELECT
    DL.empID,
    DL.name,
    DL.attDate,
    EA.hourIn,
    EA.hourOut,
    EA.etc
FROM DateList DL
LEFT OUTER JOIN EmployeeAttendance EA
ON DL.EmpID = EA.EmpID AND 
DL.AttDate = EA.AttDate
ORDER BY DL.AttDate,DL.EmpId

SQL Fiddle

Raj