Databases
StackOverflow2013
Postsdbo
POSelect DISTINCT field when using PATINDEX
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect DISTINCT field when using PATINDEX
    text
    copied!<p>I have a table called <code>Issues</code> that I need to get data from. I am joining the Issues table with another table called <code>IssueActivities</code>. So for each instance of an IssueID, there could be 1 to many IssueActivities. In the <code>IssueActivities</code> table is a field called <code>Notes</code> and it is of datatype <code>text</code>. I'm trying to select a DISTINCT list of IssueID's where the Notes field DOES NOT contain 2 particular string.</p> <p>Here's my SQL:</p> <pre><code>SELECT DISTINCT i.IssueID FROM Issues i INNER JOIN IssueActivities ia ON i.IssueID = ia.IssueID WHERE i.IssueStatusID = 2 --Closed issues only AND (PATINDEX('%Pending DR%', ia.Notes) &lt; 1 AND PATINDEX('%Pending E%', ia.Notes) &lt; 1) </code></pre> <p>The problem with this sql is that it returns IssueID's for issues that have that criteria because of the fact an Issue can have <strong>many</strong> IssueActivities, so not all the rows contain that criteria. Does that make sense? Here's a quick example:</p> <p><strong>Issues table</strong></p> <pre><code>IssueID | IssueStatusID ----------------------- 1700 2 1701 2 </code></pre> <p><strong>IssueActivities table</strong></p> <pre><code>IssueActivityID | IssueID | Notes --------------------------------- 1 1700 Issue Entered 2 1700 Sub Status changed from New to In Progress 3 1700 Sub Status changed from In Progress to Pending DR 4 1701 Issue Entered 5 1701 Issue Assigned 6 1701 Sub Status changed from New to Closed </code></pre> <p>So from the above table, I would like to get only issue 1701 because of all the IssueActivities that belong to it, none of them contain the criteria that I am using.</p> <p>Any help is greatly appreciated.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload