I have with columns for ID and Service. An ID may appear in more than one row, but each row will have a different service.
I want to show rows for IDs to that NOT have a specific service. However, I can't get SQL Server to exclude the whole ID; it seems to only exclude the specific rows that match the excluded service.
Here is what I have so far:
SELECT
distinct id ,
it.service
FROM [system].[dbo].[Data] AQ
INNER JOIN [system].[dbo].[data_Items] it on it.id= AQ.id
WHERE it.service != 'medical'
OUTPUT
| id | service |
|---|---|
| 1234 | IT support |
| 1234 | Other |
This query excluded just the rows with medical service.
What I need is that if the ID has a medical service, it never appears even if it has other services.