Missy Missy - 16 days ago 5
SQL Question

SQL Cross Join and IIF(ISNULL) - Not Working as Expected

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
, ut.emailaddress
, r.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


For each user, I get a complete set of reports - so the cross join works - but either they get a 1 for all the reports or a 0 for all their reports. I don't understand why this query isn't working. Kindly help if you can. Thanks in advance!!!

Answer

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.??;

The ?? is for the field used to identify the report. I might guess that it is reportID.

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 IS NULL.