CODEWITHSUNDEEP
sqlt-sqljoin
Greg
Greg

Reputation: 197

Sql self join where not exists?

I have a table something like the following:

name    | language | text  
-----------------------------
greet   | 1        | hello
greet   | 2        | bonjour
blue    | 1        | blue
blue    | 2        | bleu 
red     | 1        | red
green   | 1        | green
yellow  | 1        | yellow

I need to retrieve all the records which only exist for language number 1, so in the above example I should only have a list containing red, green and yellow. I don't really know much about sql joins so not sure what would be the best way to go about this? Any help would be appreciated

Upvotes: 1

Views: 4750

Answers (4)

paparazzo
paparazzo

Reputation: 45096

using a jion

SELECT t1.*
  FROM      TableName t1 
  left join TableName t2 
    on t2.name = t1.name 
   and t1.language =  1
   and t2.language <> 1
 where t2.name is null

Upvotes: 1

Vipul Rathod
Vipul Rathod

Reputation: 11

Filter records using NOT IN

SELECT *
FROM TABLENAME
WHERE LANGUAGE = 1
AND  NAME NOT IN
(
   SELECT NAME FROM TABLENAME 
   WHERE   LANGUAGE <> 1
)

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460138

Use NOT EXISTS:

SELECT t.*
FROM dbo.TableName t
WHERE t.language = 1
AND NOT EXISTS
(
   SELECT 1 FROM dbo.TableName t2
   WHERE t.name = t2.name
   AND ( t2.language IS NULL OR t2.language <> 1 )
)

DEMO

I've included t2.language IS NULL to show you how to handle NULL-values, if it's a non-null column you only need t2.language <> 1. This is the most readable and efficient approach that has no issues with NULL values. There are others.

Upvotes: 6

Michael Mairegger
Michael Mairegger

Reputation: 7301

You do not need a JOIN just use the WHERE keyword.

SELECT * FROM translationTable WHERE language = 1

Upvotes: -1

Related Questions