replace multiple left joins in access sql

Question

I m writing a query in ms access for three tables "Process Name", "Work_Log1" and "ACn_reviewer".

Now the "Work_Log1" table is having foreign keys to the "Process NAme" and "ACN_REviewer" table.

But the "Work_log1" table is having null values in some rows for "ACN_Reviewer" table.

THe working query i have come up with

select w.[activity_id], w.[activity_start_date], w.[reporting month] , 
r.[Reviewer_Name] , p.[process_name]
from [process name] p left join 
([work_log1] w left join [acn_reviewer] r
on w.[ACN Reviwer] = r.[ACN_Reviewer_ID]  )
on w.[process] = p.[process_id] 
 where w.[activity_id] = 54447 

now i know that there are no process ids null in the work_log1 table.

The question i ask is there some way to do the above query without the joins?

Something like this, left join only on acn_reviewer

 select w.[activity_id], w.[activity_start_date], w.[reporting month] , 
r.[Reviewer_Name] , p.[process_name]
from [process name] p  ,   [work_log1] w left join [acn_reviewer] r
 on w.[ACN Reviwer] = r.[ACN_Reviewer_ID]   
  where w.[process] = p.[process_id] and 
  w.[activity_id] = 54447 

I hope now i m clear

So can i write something like this in the sql, which is throwing error in ms-access

select w.[activity_id], w.[activity_start_date], w.[reporting month] ,
r.[Reviewer_Name] , p.[process_name] from [process name] p, [work_log1] w 
left join [acn_reviewer] r
on w.[ACN Reviwer] = r.[ACN_Reviewer_ID]  on w.[process] = p.[process_id]
 where w.[activity_id] = 54447 ;

Thanks

Pradyut

Answer 1

last try: i am thinking.... maybe you want to union those results to another select with work_log1 left join'd to acn_reviewer (without joining work_log1 with process first) to get the situations where process is null and acn_reviewer is not? yes? no...? who knows. i'm tired. Access doesn't allow for full outer joins and stuff so.... maybe that's what you're trying to do, actually?

        select  pw.[activity_id], 
                pw.[activity_start_date], 
                pw.[reporting month] ,
                r.[Reviewer_Name], 
                pw.[process_name] 

        from    ([work_log1] as w

                join [process name] as p 
                on w.[process] = p.[process_id] ) as pw

                    join [acn_reviewer] as r
                    on pw.[ACN Reviwer] = r.[ACN_Reviewer_ID]  

        where   pw.[activity_id] = 54447 

        -- all records in work_log1 that have a matching row both
                    -- in acn_reviewer and process via the join 

        union 


        select  pw.[activity_id], 
                pw.[activity_start_date], 
                pw.[reporting month] ,
                null as [Reviewer_Name], 
                pw.[process_name] 

        from    ([work_log1] as w

                join [process name] as p 
                on w.[process] = p.[process_id] ) as pw

                    left join [acn_reviewer] as r
                    on pw.[ACN Reviwer] = r.[ACN_Reviewer_ID]  

        where   pw.[activity_id] = 54447 and r.[acn reviewer] is null

        -- all records where work_log1, WITH a row in process, 
                    -- WITHOUT a row in acn_reviewer 

        union

        select  pw.[activity_id], 
                pw.[activity_start_date], 
                pw.[reporting month] ,
                r.[Reviewer_Name] as [Reviewer_Name], 
                null as [process_name] 

        from    ([work_log1] as w

                join [acn_reviewer] as r
                on w.[ACN Reviwer] = r.[ACN_Reviewer_ID] ) as wr

                    left join [process name] as p 
                    on w.[process] = p.[process_id]  

        where   pw.[activity_id] = 54447 and p.[process_id] is null

        -- all records where work_log1, WITHOUT a row in process, 
                    -- WITH a row in acn_reviewer 

        union


        select  pw.[activity_id], 
                pw.[activity_start_date], 
                pw.[reporting month] ,
                null as [Reviewer_Name], 
                null as [process_name] 

        from    ([work_log1] as w

                left outer join [acn_reviewer] as r
                on w.[ACN Reviwer] = r.[ACN_Reviewer_ID]  ) as wr

                    left outer join [process name] as p 
                    on wr.[process] = p.[process_id]

        where   pw.[activity_id] = 54447 and p.[process_id] is null and wr.[ACN_Reviewer_ID] is null

        -- all records in work_log1 that have no matching rows in either process or acn_reviewier
                    --  cuzzz its a left outer join and the where says the joined tables return null-thing

Access is odd and may or may not like it if you put that whole thing into a select * from (query with unions) as x where activity_id = 54447 but i am not sure. i haven't tested this. but you get the idea hopefully. and hopefully this is what you want. *IF NOT ---> an ERD like njk provided us/you is correct (b/c you said his was wrong) would be extremely helpful and without it or without an example result set where it shows the NULLs that you need I just can't continue. *

or... your first query stripped of the joins?

select  w.[activity_id], 
        w.[activity_start_date], 
        w.[reporting month], 
        r.[Reviewer_Name], 
        p.[process_name]

from    work_log1 as w, acn_reviewer as r, process as p 

where   (w.[acn reviewer] = r.acn_reviewer_id or r.acn_reviewer_id is null) and 
        w.process = p.process_id 

Answer 2

From what I gathered from the attributes above:

Process Name (p)
p.[process_name]
p.[process_id]

Work_Log1 (w)
w.[activity_id]
w.[process]
w.[acn reviwer]
w.[activity_start_date]
w.[reporting month]

ACN Reviewer (r)
r.[acn_reviewer_id]
r.[reviewer_name]

How are Process Name and ACN Reviewer related? If they aren't, there is no way to bridge them based on a NULL value in Work_Log1.