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.
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
answered Dec 23 '09 at 12:39 PM