CODEWITHSUNDEEP
sqlmultiple-records
RonT
RonT

Reputation: 31

SQL - exclude multiple rows if one of the rows meets the condition

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>

Upvotes: 3

Views: 7187

Answers (2)

sgeddes
sgeddes

Reputation: 62841

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
)

Upvotes: 5

radar
radar

Reputation: 13425

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

Upvotes: 3

Related Questions