sql NOT IN

aneilg
aneilg used Ask the Experts™
on
i have the following query.
my mappings table has 10 rows.
my execution log has 8 rows.

what i want to do is display users that are in the mappings but not in execution


SELECT DISTINCT


      b.Prac_Code,
      b.Windows_Login
      
FROM


      ReportServer.dbo.ExecutionLogStorage el with (nolock)
            left outer JOIN [Dashboards].UrgentCare.tbl_User_Mappings b  
                  ON el.UserName collate SQL_Latin1_General_CP1_CI_AS = b.Windows_Login

            and
            
            el.UserName NOT IN
            (SELECT el.UserName collate SQL_Latin1_General_CP1_CI_AS el
                  FROM [Dashboards].UrgentCare.tbl_User_Mappings b)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
SELECT DISTINCT
       b.Prac_Code,
       b.Windows_Login
FROM  
      [Dashboards].UrgentCare.tbl_User_Mappings b
      LEFT OUTER JOIN ReportServer.dbo.ExecutionLogStorage el WITH (NOLOCK)
    ON  b.Windows_Login = el.UserName COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE
      el.UserName IS NULL AND b.Windows_Login IS NOT NULL

Author

Commented:
perfect thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial