CODEWITHSUNDEEP
sqlif-statementexists
Keith
Keith

Reputation: 68

SQL alter a column if exists

I have two tables. A and B. I'm trying to figure out a way to add a column in my select query that returns true or false as to whether or not there exists a record in B.

Table A
ID     Title
1      A
2      B
3      C
4      D
5      E

Table B
ID     Detail
3      foo
4      foo
4      bar
4      barfood

I want to basically "SELECT ID, Title, (Exists?) FROM A" to return

ID     Title     Exists
1      A         False
2      B         False
3      C         True
4      D         True
5      E         False

Table A's ID column will always be unique. Table B's ID column can have zero, one, or many relating back to table A's ID. I don't care about the detail in table B, I just want to know if there is at least one record in table B that relates to table A's ID.

I'm new to SQL and I've been searching for ways to use 'if exists' or any other way to parse this out but I'm not really finding what I'm looking for.

Upvotes: 0

Views: 2019

Answers (3)

beercodebeer
beercodebeer

Reputation: 990

There are probably more efficient ways to accomplish it, but a combination of count and a case statement will do the trick:

select ID, Title, 
case when 
    (select count(1) from B where ID = A.ID) = 0 then 'False'
else 'True'
end as 'Exists'
from A

SQLFiddle link

Upvotes: 1

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36601

If you're adding a column named 'Exists' temporary then try this

select a.id, a.title,case when a.id=b.id then 'True' else 'False' end as Exists       
from A a left outer join B b
on a.id = b.id

If you've alredy added Exists column to table then 

select a.id, a.title,Exists=(case when a.id=b.id then 'True' else 'False')      
from A a left outer join B b
on a.id = b.id

Upvotes: 1

Avitus
Avitus

Reputation: 15958

if you left join table B then you'll get that information

select a.id, a.title, 
case 
  when b.id is null then 'false'
  else 'true'
end
from a
left outer join b on a.id = b.id
group by a.id, a.title

Upvotes: 0

Related Questions