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.
OR
OR
These are listed -- somewhat surprisingly -- in order of expected performance (my intuition is the JOIN would be faster, by my intuition is wrong here, which is why we profile). Regardless, it's worth knowing all three techniques.