3

How do you exclude multiple rows if one of the rows meets the condition? 

<table>
  <th>Instruction_ID</th>
  <th>Instruction_Desc</th>
    <tr>
      <td>1</td>
      <td>Please use these products:</td>
    </tr>
      <tr>
      <td>1</td>
      <td>Kerlix</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Sodium Chloride</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Tegaderm</td>
    </tr>
    
    <tr>
      <td>2</td>
      <td>Please use these products</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Sodium Chloride</td>
    </tr>
</table>

I'm trying to exclude all rows for a given instruction_id if one of the rows in the group has the word "Kerlix." The desired output would be:

<table>
  <th>Instruction_ID</th>
  <th>Instruction_Desc</th>
    <tr>
      <td>2</td>
      <td>Please use these products</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Sodium Chloride</td>
    </tr>
</table>
Tunaki
  • 132,869
  • 46
  • 340
  • 423
RonT
  • 31
  • 1
  • 3

2 Answers2

5

There are a couple of ways to do this. Here's one using NOT IN:

SELECT *
FROM Table1
WHERE Instruction_ID NOT IN (
    SELECT Instruction_ID
    FROM Table1
    WHERE Comments LIKE '%Kerlix%'
)

And here's one using NOT EXISTS:

SELECT *
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM Table1 t2
    WHERE Comments LIKE '%Kerlix%' AND t1.Instruction_Id = t2.Instruction_Id
)
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • would `SELECT distinct Instruction_ID` help? – Beth Nov 26 '14 at 22:44
  • @Beth -- um, how? Not sure I'm understanding your comment. I don't see any reason to add the additional overhead of `distinct` in this case. – sgeddes Nov 26 '14 at 22:54
  • that's what I was wondering, if adding the `distinct` clause would improve performance. You're saying it wouldn't? That the work done by `distinct` takes longer than joining duplicate values? – Beth Nov 26 '14 at 23:00
  • 1
    @Beth -- correct, in this case, `distinct` would only add to the overhead of the query. Perhaps this is what you were thinking of: http://stackoverflow.com/questions/12201885/how-to-determine-what-is-more-effective-distinct-or-where-exists – sgeddes Nov 26 '14 at 23:06
3

You can do self join AND use left join

SELECT T1.Instruction_ID, T1.Comments
FROM Table1 T1
LEFT JOIN Table1 T2
ON T1.Instruction_Id = T2.Instruction_Id
and T2.Comments LIKE '%Kerlix%'
WHERE T2.Instruction_Id is NULL
radar
  • 13,270
  • 2
  • 25
  • 33