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.
>Solution :
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