SQL Server excluding Orders from a search-CodePudding

I have a task to exclude Orders that have a specific 'Note'. The issue is that one Order can have multiple 'Notes'. And the table looks like this:

OrderID | Notes
1234, 'Test'
1234, 'Good'
1234, 'Bad'

If I use this line WHERE NOTE NOT LIKE '%Test%' this removes only the Order with 'Test' in the Notes column and Order 1234 still shows in the search.

Any idea on how to remove all records if the Order has 'Test'in the notes?

This is my query:

SELECT * FROM tbl.ORDER
WHERE NOTE NOT LIKE '%Test%'

This query returns:

Order | Notes 

1234, 'Good'
1234, 'Bad'

I need them all gone if the Order has 'Test' as a note.

CodePudding user response：

Use NOT EXISTS:

SELECT *
FROM tbl.Order o
WHERE NOT EXISTS(SELECT 1 FROM tbl.Order
                 WHERE Order = o.Order
                 AND Notes LIKE '%Test%')

CodePudding user response：

This query selects all orders which have not "Test" in any note:

Select * FROM tbl.ORDER
Where  Order not in (
   SELECT Order FROM tbl.ORDER
   WHERE NOTE LIKE '%Test%'
)

If that is not what you need provide expected result please

CodePudding user response：

The simplest way to do it - subquery:

SELECT * FROM Order where tbl.Order not in (
   SELECT Order as OrderId FROM tbl.ORDER
     WHERE NOTE NOT LIKE '%Test%')

CodePudding user response：

For example you can filter with a subquery, getting the OrderId's that you don't want to appear in the result, and using the "NOT IN" clause:

SELECT * FROM tbl.ORDER
WHERE OrderId NOT IN (SELECT OrderId FROM tbl.ORDER WHERE NOTE = 'Test')