CODEWITHSUNDEEP
sql-serversql-server-2012
user1804925
user1804925

Reputation: 159

Add a column to table a, if the values exist in table a and table b

I know how to check if the values exist in both table, but how can I add a column indicate it find something

SELECT NAME, ID FROM TABLE_A TA
WHERE EXISTS (SELECT 1 FROM TABLE_B TB
              WHERE TA.ID = TB.ID)

Result

NAME  ID
1    123
2    234
3    345

What I want

NAME  ID    Exists
1    123     Y
2    234     N
3    345     N

Upvotes: 0

Views: 62

Answers (4)

HLGEM
HLGEM

Reputation: 96552

SELECT NAME, COALESCE(TA.ID, TB.ID), 
CASE WHEN TB.Id IS NULL OR TA.ID IS NULL THEN 'N' else 'Y' end [Exists] 
FROM TABLE_A TA
FULL OUTER JOIN  TABLE_B TB
 ON TA.ID = TB.ID

This will give you the results for both tables, so if an id exists in B but not A, you will also get resutls.

If you only need the reults for values in Tablea that are or are not in B

SELECT NAME, TA.ID, CASE WHEN TB.Id IS NULL  THEN 'N' else 'Y' end [Exists] 
FROM TABLE_A TA
LEFT OUTER JOIN  TABLE_B TB
 ON TA.ID = TB.ID

Upvotes: 0

Pu297
Pu297

Reputation: 151

SELECT TA.NAME, TA.ID,CASE WHEN TB.ID IS NULL THEN 'N' ELSE 'Y' END AS Y_N
FROM 
(select NAME, ID FROM TABLE_A)TA LEFT JOIN
(SELECT ID FROM TABLE_B)TB ON 
          TA.ID = TB.ID

Upvotes: 1

Gabor Rajczi
Gabor Rajczi

Reputation: 471

You can use the exists criteria in a case statement:

SELECT NAME, ID, CASE WHEN EXISTS (SELECT 1 FROM TABLE_B TB
              WHERE TA.ID = TB.ID) THEN 'Y' else 'N' end [Exists] FROM TABLE_A TA

Upvotes: 2

mohan111
mohan111

Reputation: 8865

may be this works 

SELECT NAME, ID,case when 
    (select count(1) from TABLE_B where ID = TA.ID) = 0 then 'N'
else 'Y'
end as 'Exists' FROM TABLE_A TA
WHERE EXISTS (SELECT 1 FROM TABLE_B TB
              WHERE TA.ID = TB.ID)

Upvotes: 2

Related Questions