SQL QUERY FOR selecting only products which does not contain one language

I need to select only those product which is not having language-id "en-ZA".

In the below example I need to select only product number "102".

You can achieve this by following query using CASE WHEN

SELECT ProductNumber 
FROM TableName
GROUP BY ProductNumber
HAVING SUM(CASE WHEN [Language-ID]='En-ZA' THEN 1 ELSE 0 END) = 0

The same query can be written using NOT EXISTS like the following.

SELECT ProductNumber
FROM TableName t1
WHERE NOT EXISTS (
  SELECT 1
  FROM TableName  t2
  WHERE [Language-ID] = 'En-ZA'
  AND t2.ProductNumber = t1.ProductNumber
)
GROUP BY ProductNumber

Try to identify Product numbers which may contain "en-ZA" by using:

select distinct ProductNumber
from TABLE
where Language-ID = 'en-ZA'

That gives you a list of all Product numbers which contain 'en-ZA'.

Using that list you can sort your main list like:

select ProductNumber, Language-ID
from TABLE
where ProductNumber not in (
  select distinct ProductNumber
  from TABLE
  where Language-ID = 'en-ZA'
)

This is a way to do it using left join :

select t.ProductNumber
from (
  select distinct ProductNumber
  from mytable
  where Language_ID != 'En-ZA'
) t
left join (
  select *
  from mytable
  where Language_ID = 'en-ZA'
) as s on s.ProductNumber = t.ProductNumber
where s.ProductNumber is null

We determine the rows where Language_ID != 'En-ZA' (100,101,102) and match it with the ones having Language_ID = 'en-ZA' (100,101), then select only the ones that are not matched using where s.ProductNumber is null

Demo here

use NOT EXISTS - it is simple and quite fast

select ProductNumber, Language-ID
from YourTable as YT1
where not exists (
  select 1
  from YourTable as YT2
  where YT2.Language-ID = 'en-ZA'
  and YT2.ProductNumber = YT1.ProductNumber
)