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 3 (of 3 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: 246978 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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!