Can SQL do this condition?

Question

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.

Answer 1

-- not exists
SELECT id, service
FROM data_items di
WHERE NOT EXISTS( 
   SELECT 1
   FROM data_items
   WHERE id = di.id AND service = 'medical'
)

OR

-- not in
SELECT id, service
FROM data_items
WHERE id not IN (SELECT id FROM data_items WHERE service='medical')

OR

-- exclusion join
SELECT di0.id, di0.service
FROM data_items di0
LEFT JOIN data_items di1 ON di1.id = di0.id AND di1.service = 'medical'
WHERE di1.id IS NULL

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.

Answer 2

Another way to achieve the wanted result is via a group by query and use of the having clause.

SELECT id, service
FROM data_items di
GROUP BY id, service
HAVING MAX(case when service = 'medical' then 1 else 0 end) = 0

Notes. As your original query is using select distinct, this approach will also reduce the rows to a minimum.

Answer 3

You can use a window function for this. This requires only a single scan of the base table, no self-joins are required.

SELECT 
  it.id,
  it.service
FROM (
    SELECT *,
      is_medical = COUNT(CASE WHEN it.service = 'medical' THEN 1 END)
    FROM dbo.Data AQ
    INNER JOIN dbo.data_Items it ON it.id = AQ.id
) it
WHERE it.is_medical = 0;