I want all rows but not those in a table

Question

Post reply

I want all rows but not those in a table

bobh0526

SSC-Addicted

Points: 489
More actions
June 12, 2017 at 9:31 am

#331067

I have the following SQL statement in a stored procedure:
SELECT r.ReportID,
  r.ReportName
      ,r.URL
      ,r.Description
      ,r.FEX_Name
      ,r.ReportPurpose
      ,r.ReportConstraints
      ,r.Misc
      ,r.IsSecured
      ,r.IsActive
         ,CASE WHEN du.ID is null THEN 0 ELSE 1 END As IsInDshUsers
         ,rc.CreatedBy
         ,du.UserID
         ,du.ID
         ,IsNull(du.SortOrder, 0) As SortOrder
       FROM WF_Report r WITH (NOLOCK)
       INNER JOIN WF_ReportCategoryMap rcm WITH (NOLOCK)
              ON r.ReportID=rcm.ReportID
       INNER JOIN WF_ReportCategory rc WITH (NOLOCK)
              ON rc.CategoryID=rcm.CategoryID
       LEFT OUTER JOIN dsh_users du WITH (NOLOCK)
              ON rc.CreatedBy = du.UserID AND rcm.ReportID = du.ReportID
       WHERE rc.CategoryName IN ('aaa', 'bbb', 'ccc')
I don't want to return any rows where rows exist in table dsh_users. I wasn't sure how to do this. Can anyone help?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

AND du.UserID IS NULL;

And ditch the NOLOCK hints, unless you have a very good reason for having them there.

John

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

bobh0526 - Monday, June 12, 2017 9:31 AM
I have the following SQL statement in a stored procedure:
SELECT r.ReportID,
  r.ReportName
      ,r.URL
      ,r.Description
      ,r.FEX_Name
      ,r.ReportPurpose
      ,r.ReportConstraints
      ,r.Misc
      ,r.IsSecured
      ,r.IsActive
         ,CASE WHEN du.ID is null THEN 0 ELSE 1 END As IsInDshUsers
         ,rc.CreatedBy
         ,du.UserID
         ,du.ID
         ,IsNull(du.SortOrder, 0) As SortOrder
       FROM WF_Report r WITH (NOLOCK)
       INNER JOIN WF_ReportCategoryMap rcm WITH (NOLOCK)
              ON r.ReportID=rcm.ReportID
       INNER JOIN WF_ReportCategory rc WITH (NOLOCK)
              ON rc.CategoryID=rcm.CategoryID
       LEFT OUTER JOIN dsh_users du WITH (NOLOCK)
              ON rc.CreatedBy = du.UserID AND rcm.ReportID = du.ReportID
       WHERE rc.CategoryName IN ('aaa', 'bbb', 'ccc')
I don't want to return any rows where rows exist in table dsh_users. I wasn't sure how to do this. Can anyone help?

Here is another version.
SELECT r.ReportID , r.ReportName , r.URL , r.Description , r.FEX_Name , r.ReportPurpose , r.ReportConstraints , r.Misc , r.IsSecured , r.IsActive , rc.CreatedBy FROM WF_Report r JOIN WF_ReportCategoryMap rcm ON r.ReportID = rcm.ReportID JOIN WF_ReportCategory rc ON rc.CategoryID = rcm.CategoryID WHERE rc.CategoryName IN ('aaa', 'bbb', 'ccc') AND NOT EXISTS ( SELECT 1 FROM dsh_users du WHERE rc.CreatedBy = du.UserID AND rcm.ReportID = du.ReportID );

I've removed the nasty NOLOCK hints and also the columns which reference the dsh_users table ... as you are looking for rows which do not exist on this table, all of the values will be NULL by definition.

bobh0526 SSC-Addicted Points: 489 More actions · Answer 3

bobh0526

SSC-Addicted

Points: 489

June 12, 2017 at 10:59 am

#1946434

Thanks man. That worked!