SQL Query help

Transact-SQL
1

I have a query to pull out all records that don’t have a pdf attachment. Currently my query is doing this but I would also like for the RecordId column to only display the RecordId once if there happens to be more than 1 record per RecordId.

SELECT         	a.IsDocument, a.Subject, a.NoteText, a.MimeType, a.FileName,e.Name 
AS entity, a.ObjectTypeCode,a.ObjectId AS RecordId
FROM           Annotation a
			INNER JOIN EntityView e ON e.ObjectTypeCode = a.ObjectTypeCode
WHERE        
			(a.FileName = N'DISTINCT') AND 
			(CONVERT(VARCHAR(255),a.objectId) = N'DISTINCT') and
			((a.MimeType IS NULL) AND (a.IsDocument = 0)) 
			or ((a.MimeType <> 'application/pdf') AND (a.IsDocument = 1)) 
ORDER BY e.Name
2

What the Data should look like:

IsDocument:
1
1
1
1
1
1

Subject:
File Attachment
File Attachment
File Attachment
File Attachment
File Attachment
Note created on 5/03/2014 1:12 PM by KB

Note Text:
NULL
NULL
Attached current Form for Position
NULL
NULL
Email Message

Mime Type:
text/html
application/octet-stream
application/msword
application/msword
application/msword
text/html

FileName:
Rogers, J Fax Report.msg
Joe Rogers Estimate Request Sheet Form.doc
Robinson Service History.docx
Sharp, Marilyn J-Deductions.docx
Brown,K Review Sheet Form.doc
KB Document.msg

Entity:
Account
Task
Account
Contact
Security
Security

ObjectTypeCode:
1
4212
1
2
10023
10023

RecordId:
0EF5E941
<---------this is a duplicate RecordId
D031FE6E
AE532068
57F5E941
<---------this is a duplicate RecordId

3

I took the liberty of editing your where statement, to what I think you intended (I might be wrong).
Try this:

SELECT TOP(1) WITH TIES
       a.IsDocument
      ,a.Subject
      ,a.NoteText
      ,a.MimeType
      ,a.FileName
      ,e.Name AS entity
      ,a.ObjectTypeCode
      ,a.ObjectId AS RecordId
  FROM Annotation AS a
       INNER JOIN EntityView AS e
               ON e.ObjectTypeCode = a.ObjectTypeCode
 WHERE a.FileName = N'DISTINCT'
   AND CONVERT(VARCHAR(255),a.objectId) = N'DISTINCT'
   AND ((a.MimeType IS NULL
   AND   a.IsDocument = 0
        )
    OR  (a.MimeType <> 'application/pdf'
   AND   a.IsDocument = 1
       ))
 ORDER BY ROW_NUMBER() OVER(PARTITION BY a.ObjectId
                                ORDER BY e.Name
                           )
;
4

Thanks for responding.

When I run your query, I now end up getting no results.

5

Probably because of the change I did to your where statement. Try putting back your original where statement (but leave the "select top(1) with ties" and "order by" statements intact.

1 Like
6

That worked! Thanks soo much!