combining two queries - 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 2005 Forums

Transact-SQL (2005)

combining two queries

Author

Topic

jose john
Starting Member

2 Posts

Posted - 2007-10-11 : 09:29:30

Hi All,

I will describe the scenario :
Suppose there is a table called X with two columns A and B
I have two queries for this table
1. select * from X where A = 'a' and B='b';
2. select * from X where A= 'a';

My requirement is if there is some results from the first query then return the results otherwise execute the second one and return the results.But I have provision to execute only one query. How to achieve this in a single query execution? Can anyone shed some light??

Thanks,
JJ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 09:32:43

if exists (select * from X where A = 'a' and B='b')
select * from X where A = 'a' and B='b'
else
select * from X where A= 'a';

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 09:42:14

Or this

-- Prepare sample data
DECLARE	@x TABLE (a CHAR(1), b CHAR(1))

INSERT	@x
SELECT	'a', 'b' UNION ALL
SELECT	'a', 'z'

-- Show the expected output
SELECT		t.a,
		t.b
FROM		@x AS t
INNER JOIN	(
			SELECT		a,
					CASE SUM(CASE WHEN b = 'b' THEN 1 ELSE 0 END)
						WHEN 0 THEN '%'
						ELSE 'b'
					END AS b
			FROM		@x
			WHERE		a = 'a'
			GROUP BY	a
		) AS y ON y.a = t.a
WHERE		t.b LIKE y.b

Say hi to your professor from me!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 09:44:39

[code]-- Show the expected output
SELECT t.a,
t.b
FROM @x AS t
WHERE t.a = 'a'
AND EXISTS (SELECT * FROM @x AS y WHERE y.b = 'b' AND y.b = t.b)

UNION ALL

SELECT t.a,
t.b
FROM @x AS t
WHERE t.a = 'a'
AND NOT EXISTS (SELECT * FROM @x AS y WHERE y.b = 'b')[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 09:48:50

[code]-- Show the expected output
SELECT TOP 1 WITH TIES
a,
b
FROM @x
ORDER BY MIN(CASE WHEN b = 'b' THEN 0 ELSE 1 END) OVER (PARTITION BY b)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 09:51:37

[code]-- Show the expected output
SELECT TOP 1 WITH TIES
a,
b
FROM (
SELECT a,
b,
0 AS HitB
FROM @x
WHERE a = 'a'
AND b = 'b'

UNION ALL

SELECT a,
b,
1
FROM @x
WHERE a = 'a'
) AS d
ORDER BY HitB[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 09:55:07

I would guess the one posted 10/11/2007 : 09:42:14 is the most efficient and fastest.

E 12°55'05.25"
N 56°04'39.16"

jose john
Starting Member

2 Posts

Posted - 2007-10-11 : 09:59:27

Thanks for all your replies,

Actually i want a solution in pure sql.....not in T-SQL. That too i should be able to execute the entire query in a single execute statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 10:10:00

I can see FOUR solutions meeting that criterias!

Please let us see what YOU have come up with this far...

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources