x

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.

more ▼

asked Dec 23, 2009 at 07:33 AM in Default

Redgen gravatar image

Redgen
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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'
more ▼

answered Dec 23, 2009 at 12:39 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x115
x49

asked: Dec 23, 2009 at 07:33 AM

Seen: 1002 times

Last Updated: Dec 23, 2009 at 12:35 PM