Outer Join in Access: How to use 2 outer joins on 1 table
Please see this query :-
select * from
(
select A.emp_id as EmpID,A.card_no as Card_Num,A.emp_fullname as Emp_Name,
B.iodate as L_Date ,A.Department as Emp_Deptt,A.DOJ,
min(B.iotime) as In_Time, C.start_time as Flexi_Time,
datediff('n',C.start_time,in_time) as Minutes_diff,
datediff('s',C.start_time,in_time) as Seconds_diff,
E.Emp_Comments as emp_comments
from
(
(
employee_master as A
inner join flexitimings_master as C
on A.flexi_timing=C.slot_id
)
left outer join Reason_Transaction as E
on A.Emp_id=E.Emp_id
)
left outer join iodata as B
on B.iodate=E.date1
where A.active='1'
and A.card_no=B.cardno
and b.iodate>=A.DOJ
and Weekday(b.iodate)<>1
and Weekday(b.iodate)<>7
and b.iodate not in (select holiday_date from holidays)
group by A.emp_id,A.card_no,A.emp_fullname,B.iodate,A.department,A.DOJ,
C.start_time,emp_comments
having (B.iodate) between format(cdate("11/01/2009"),"mm/dd/yyyy")
and format(cdate("11/30/2009"),"mm/dd/yyyy")
order by A.Emp_fullName,A.emp_id,b.iodate
)
where seconds_diff>900;
It is still somehow working as Inner Join.Plz help.
You have
left outer join iodata as B
on B.iodate=E.date1
but in your WHERE clause you have
and A.card_no=B.cardno
and b.iodate>=A.DOJ
and Weekday(b.iodate)<>1
and Weekday(b.iodate)<>7
and b.iodate not in (select holiday_date from holidays)
all referencing the table "B" - so any rows from the LEFT JOIN iodata which are missing will fail the WHERE clause, and thus be excluded.
You could change the syntax to move all the WHERE clause statements referencing table "B" to the JOIN, and then it would only select rows from "B" where B.iodate=E.date1 and also where the other conditions applied:
left outer join iodata as B
on B.iodate=E.date1
and B.cardno = A.card_no
and b.iodate>=A.DOJ
and Weekday(b.iodate)<>1
and Weekday(b.iodate)<>7
and b.iodate not in (select holiday_date from holidays)
WHERE A.active='1'
