Select within Select returning a condition

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)

Select within Select returning a condition

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2005-10-21 : 11:42:16

I have a select stored procedure in which I need to check for the presence of records in another table based on the current record id. If records exist, I need to show a 1 else show a zero.

Sample:
SELECT
myID,
fName,
lName,
fullName,
<records exist in another table for this user> AS bHasRec
FROM
tUSERS

The condition on the other table would be like:
if (SELECT Count(detailID) from tDetails) > 0
Select @i = 1
else
Select @i =0

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-21 : 13:00:15

Whats up David. Use a CASE like so to acheive this:

set nocount on


declare @tUsers table (myID int, fName varchar(25), lName varchar(25), fullName varchar(25))
insert into @tUsers
	select 1, 'Nathan', 'Skerl', 'Nathan L Skerl' union
	select 2, 'Adam', 'Skerl', 'Adam J Skerl'

declare @tDetails table (detailID int, myID int)
insert into @tDetails
	select 1, 1 union select 2, 1

SELECT	myID,
	fName,
	lName,
	fullName,
	case 
		when(select count(*) from @tDetails where myID = t.myID) > 0 then 1
		else 0
	end as 'bHasRec'
FROM @tUsers t

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-21 : 13:04:31

Could use a join as well, not sure which would be faster. Do some perf testing and post back here with any feedback.

SELECT	t.myID,
	t.fName,
	t.lName,
	t.fullName,
	case 
		when d.cnt > 0 then 1
		else 0
	end as 'bHasRec'
FROM @tUsers t left join (select myID, count(*) cnt from @tDetails group by myID) d
on t.myID = d.myID

Nathan Skerl

davidliv
Starting Member

45 Posts

Posted - 2005-10-21 : 13:32:51

Thanks nathans.
I'm having one of those days I guess. Couldn't come up with the CASE concept. Works perfect.

Thanks again.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-21 : 13:39:23

No problem, glad to help.

It looks like they generate identical execution plans.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources