SP Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP Help

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2007-03-05 : 09:20:37

I need to find if a value exists in another table so that I will know how to handle it within an application:

TableA:
AID, Title, Desc
1 tit1 Desc1
2 ti2 Desc2
3 Ti3 Desc3
4 T14 desc4

TableB:
BID AID Comment
1 1 blah blah
2 3 mmmmm

Result:

AID Title Desc InUse
1 tit1 Desc1 1
2 ti2 Desc2 0
3 Ti3 Desc3 1
4 T14 desc4 0

If anyone can help, I would appreciate it - thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 09:26:58

[code]
declare @TableA table
(
AID int,
Title varchar(10),
[Desc] varchar(10)
)
insert into @TableA
select 1, 'tit1', 'Desc1' union all
select 2, 'ti2', 'Desc2' union all
select 3, 'Ti3', 'Desc3' union all
select 4, 'T14', 'desc4'

declare @TableB table
(
BID int,
AID int,
Comment varchar(10)
)
insert into @TableB
select 1, 1, 'blah blah' union all
select 2, 3, 'mmmmm'

select a.*,
InUse = case when b.AID is null then 0 else 1 end
from @TableA a left join @TableB b
on a.AID = b.AID

/*
AID Title Desc InUse
----------- ---------- ---------- -----------
1 tit1 Desc1 1
2 ti2 Desc2 0
3 Ti3 Desc3 1
4 T14 desc4 0
*/
[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-05 : 09:30:43

[code]Select t1.AID, t1.Title, t1.Desc,
Case t2.AID Is Not NULL then 1 else 0 end as InUse
From TableA t1 LEFT JOIN TableB t2
on t1.AID = t2.AID[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ann
Posting Yak Master

220 Posts

Posted - 2007-03-05 : 09:32:13

That was quick! Thanks that is exactly what I need.
I appreciate your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources