Combine two SELECT statements together

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 2005 Forums

Transact-SQL (2005)

Combine two SELECT statements together

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-01 : 11:25:11

Hi

I am running two SELECT queries, one query to return a simple BIT value, and the other to return columns from a table (although only one row is ever returned). The statements work okay at the moment, but I wondered whether it is possible to combine both statements into one? Also if they were combined, would it offer any performance benefits? There does seem to be a lot of code to achieve something so simple. I think it should be possible with CASE but I can't get it to work.


DECLARE @isAdmin BIT

IF EXISTS (	SELECT 
			* 
		FROM
			[tableX]
		WHERE
			(colY = @someVal) AND
			(colZ = @anotherVal)
	)
	BEGIN
		SET @isAdmin = 1
	END
ELSE
	BEGIN
		SET @isAdmin = 0
	END


SELECT
	t1.colA,
	t1.colB,
	t1.colC,
	t2.colD,
	t2.colE,
	@isAdmin as isAdmin -- can this be returned by combining the above into this statement?
FROM
	[table1] t1 INNER JOIN
	[table2] t2 ON t1.pkID = t2.fkID

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-01 : 11:36:41

Is tableX a totally different table or is it any one of table1 or table2?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-01 : 11:45:37

Try:


SELECT
	t1.colA,
	t1.colB,
	t1.colC,
	t2.colD,
	t2.colE,
	CAST(COALESCE(A.A, 0) AS bit) AS isAdmin
FROM table1 t1
	JOIN table2 t2
		ON t1.pkID = t2.fkID
	CROSS JOIN
	(
		SELECT TOP 1 1 AS A
		FROM tableX
		WHERE colY = @someVal
			AND colZ = @anotherVal
	) A

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-01 : 11:46:29

quote:
Originally posted by vijayisonly

Is tableX a totally different table or is it any one of table1 or table2?

Hi - its a totally different table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources