Join for testing availability - 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

General SQL Server Forums

New to SQL Server Programming

Join for testing availability

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-12 : 02:15:54

hi all,

how do i left join some table just to check the availabilty of the data.. say... if id in tblA exists in tblB, how do i just select say flag='Y' without joining the data in tblB that could have more than 1 record ?

tblA
a1
a2

tblB
a1
a1
a1

i dont want to select what i join in tblB, just to check the availability... so in this case :-
a1 ----> Y
a2 ----> N

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 02:20:53

[code]Select
a.Id, Case When b.id is null then 'N' else 'Y' end as [Exists]
from tblA a LEFT JOIN tblB b on a.id = b.id[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 02:22:00

these are those came to my mind right now


declare @tblA table
(
	col	varchar(2)
)

insert into @tblA
select	'a1'	union all
select	'a2'

declare @tblB table
(
	col	varchar(2)
)

insert into @tblB
select	'a1'	union all
select	'a1'	union all
select	'a1'

select	a.col, 
	flag	= case when exists (select * from @tblB b where b.col = a.col) then 'Yes' else 'No' end
from	@tblA a

select	a.col, 
	flag	= case when b.col is not null then 'Yes' else 'No' end
from	@tblA a left join 
	(
		select	distinct col
		from	@tblB b
	) b
	on	a.col	= b.col

select	distinct 
	a.col, 
	flag	= case when b.col is not null then 'Yes' else 'No' end
from	@tblA a left join @tblB b
	on	a.col	= b.col

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-12 : 02:22:15

ohh harsh that was really fast! lemme try it out

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-12 : 02:22:36

without join...

Select a.col1, Case when (select count(*) from tableB where col1 = a.col1) > 0 Then 'Y' else 'N' end
From tableA a

--------------------------------------------------
S.Ahamed

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-12 : 02:22:48

oh tan?? u too

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 02:25:08

nah ... i am slower by 67 secs but mine did not return duplicate record for tableA

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-12 : 02:25:32

u too pbguy?? u guys were fast..
let me check which one i like.. hahahahhaha..
err tan.. what is that? are u giving me 3 solutions? (at a glance)

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 02:29:12

you try and see which give you best performance

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-12 : 02:30:11

alrite ill go for this one.. thanks guys
select a.col,
flag = case when exists (select * from @tblB b where b.col = a.col) then 'Yes' else 'No' end
from @tblA a

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-12 : 02:31:17

Khtan won the lucky draw :-)

--------------------------------------------------
S.Ahamed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 02:32:54

are you sure the chosen one gives you best performance ?

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-12 : 02:48:57

he he.. i go for the simplest... this one seems ok.. fills up my requiremnet.. no extra un needed record..

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 02:55:08

but all 3 solution that i posted does not give you extra records

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources