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

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.

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;