I'm writing a query with a report file, an employee file and a report distribution file. I would like a list of employee names, each with every report name and a 0 if they don't get the report and a 1 if they do get the report.
select distinct ut.user_name
, iif(ISNULL(rd.employeeid,0)=0, 0,1) AS currentreport
from US_usertable ut
cross join DL_reports r
left outer join DL_Reptdistrib rd
on ut.employeeID = rd.employeeid
I think you are missing a join condition on the report:
select ut.user_name, ut.emailaddress, r.name, (case when rd.employeeid is null then 0 else 1 end) as currentreport from US_usertable ut cross join DL_reports r left outer join DL_Reptdistrib rd on ut.employeeID = rd.employeeid and r.?? = rd.??;
?? is for the field used to identify the report. I might guess that it is
Note: I switched the syntax to standard SQL.
IIF() is in SQL Server for compatibility with MS Access (why Microsoft didn't put the ANSI standard
case in MS Access is beyond me). I also replaced
ISNULL() with the ANSI standard