Show message if IN record does not exist

Asked

Viewed 36 times

0

I want to put several CPF numbers on IN of a query and if the CPF exists in the table show the data, but if it does not exist show a column indicating that it does not exist in the table. 

Exemplo: 
Select * from trabalhador where cpf in ('11111111111','22222222222','33333333333','44444444444','55555555555')

Na tabela no existe os três primeiros CPF então o resultado seria 

11111111111 Ana 
22222222222 Carla 
33333333333 Maria 
44444444444 CPF NÃO EXISTE 
55555555555 CPF NÃO EXISTE
sql firebird

  • if there is, you have to return n worker table columns, and if not, return only one ? where the query is being executed ?

1 answer

1

Try something like that:

DECLARE @TAB TABLE (
cpf NVARCHAR(255)
)

INSERT INTO @TAB
SELECT '11111111111'
UNION ALL
SELECT '22222222222'
UNION ALL
SELECT '33333333333'
UNION ALL
SELECT '44444444444'
UNION ALL
SELECT '55555555555'

Select T.*, ISNULL(C.cpf, 'NÃO EXISTE') from trabalhador T
RIGHT OUTER JOIN @TAB C
ON T.cpf = C.cpf

Browser other questions tagged sql firebird

You are not signed in. Login or sign up in order to post.