A better way to "search again" with less filters?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

A better way to "search again" with less filters??

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-09 : 19:27:43

Guys,
Is there a more efficient way to perform the types of query where you look for a direct match using two filters, but if no rows are found you look again using only one of the filters? Can we do it in one select somehow?


SELECT account
FROM test_account
WHERE attribute_1 = @attribute_1
	AND attribute_2 = @attribute_2
SELECT @row_count = @@ROWCOUNT

IF @row_count = 0
BEGIN
	SELECT account
	FROM test_account
	WHERE attribute_1= @attribute_1
END

OR

IF EXISTS(SELECT ...

Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-09 : 19:32:05

Basically, if both filters match - give me that row. Otherwise, give me the row that matches the first filter.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-09 : 19:35:07

Wouldn't you just want to use OR?



SET NOCOUNT ON

DECLARE @Table1 table (Column1 varchar(10))
DECLARE @var1 varchar(10), @var2 varchar(10)

INSERT INTO @Table1 VALUES('Tara')
INSERT INTO @Table1 VALUES('Duggan')
INSERT INTO @Table1 VALUES('John')
INSERT INTO @Table1 VALUES('Smith')
INSERT INTO @Table1 VALUES('SQLTeam')

SELECT @var1 = 'Tara', @var2 = 'Duggan'

SELECT *
FROM @Table1
WHERE Column1 = @var1 OR Column1 = @var2

SELECT @var1 = 'Tara', @var2 = 'SomeValue'

SELECT *
FROM @Table1
WHERE Column1 = @var1 OR Column1 = @var2

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-09 : 19:36:42

Here's some more sample code to match your two column search:



SET NOCOUNT ON

DECLARE @Table1 table (Column1 varchar(10), Column2 varchar(10))
DECLARE @var1 varchar(10), @var2 varchar(10)

INSERT INTO @Table1 VALUES('Tara', 'Duggan')
INSERT INTO @Table1 VALUES('John', 'Smith')

SELECT @var1 = 'Tara', @var2 = 'Duggan'

SELECT *
FROM @Table1
WHERE Column1 = @var1 OR Column2 = @var2

SELECT @var1 = 'Tara', @var2 = 'Kizer'

SELECT *
FROM @Table1
WHERE Column1 = @var1 OR Column2 = @var2

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-09 : 19:48:04

A variation that might be helpful:

SELECT CASE
WHEN attribute_1 = @attribute_1 AND attribute_2 = @attribute_2 THEN 'A'
WHEN attribute_1 = @attribute_1 THEN 'B'
ELSE 'C' END AS MatchRank, account
FROM test_account
WHERE attribute_1 = @attribute_1 OR attribute_2 = @attribute_2
ORDER BY MatchRank, account

The CASE expression will check if both columns match and give an A, B, or C you can use to rank/sort matching rows.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-09 : 20:08:31

Yea, a better an example would use 3 filters.

SET NOCOUNT ON

DECLARE @Table1 table (Column1 varchar(10), Column2 varchar(10), Column3 varchar(10))
DECLARE @var1 varchar(10), @var2 varchar(10), @var3 varchar(10), @row_count INT

INSERT INTO @Table1 VALUES('Tara', 'Duggan', 'Blue')
INSERT INTO @Table1 VALUES('Tara', 'Duggan', 'Red')

SELECT @var1 = 'Tara', @var2 = 'Duggan', @var3 = 'Black'

SELECT *
FROM @Table1
WHERE (Column1 = @var1 AND Column2 = @var2 AND Column3 = @var3)
SELECT @row_count = @@ROWCOUNT
IF @row_count = 0
BEGIN
SELECT *
FROM @Table1
WHERE (Column1 = @var1 AND Column2 = @var2)
END

This is not exactly what Im looking for... but it should help explain it a bit better.

Thanks Tara and Rob!!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-09 : 20:12:58

But ultimately I only want the best matched returned.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-09 : 20:13:37

There's no point in running the query again if you don't get a match the first time. As Tara stated, use an OR condition in the WHERE clause so that you only have to run the query once, and if need be, use the CASE expression to rank the matches. You can use a TOP 1 or TOP 10 to limit the matched rows returned.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-09 : 20:16:03

got it Rob. Im on it. Ill post back when I think its ready.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-09 : 20:50:47

Thanks again, both of you. How does this look...


SET NOCOUNT ON

DECLARE @Table1 table (Column1 varchar(10), Column2 varchar(10), Column3 varchar(10))
DECLARE @var1 varchar(10), @var2 varchar(10), @var3 varchar(10)

INSERT INTO @Table1 VALUES('Tara', 'Duggan', 'Blue')
INSERT INTO @Table1 VALUES('Tara', 'Duggan', 'Red')

SELECT @var1 = 'Tara', @var2 = 'Duggan', @var3 = 'Blue'

SELECT TOP 1 * FROM (
	SELECT 
		Column1, 
		Column2, 
		Column3, 
		CASE
			WHEN Column1 = @var1 AND Column2 = @var2 AND Column3 = @var3
			THEN 'A'
			WHEN Column1 = @var1 AND Column2 = @var2
			THEN 'B'
			ELSE 'C'
		END AS 'Rank'
	FROM @table1
	WHERE Column1 = @var1 AND Column2 = @var2
) t
ORDER BY Rank

Kristen
Test

22859 Posts

Posted - 2005-03-10 : 00:42:50

WHERE Column1 = @var1 OR Column2 = @var2 ?? Otherwise you aren't going to get any Rank=C items

We tend to use logic along the lines of


WHERE     (@var1 IS NULL OR Column1 = @var1)
      AND (@var2 IS NULL OR Column2 = @var2)

Kristen

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-10 : 18:42:48

in this particular case the ELSE 'C' is to prevent the NULLs from being ordered up top.

should actually be ORDER BY Rank DESC

Kristen
Test

22859 Posts

Posted - 2005-03-11 : 00:47:50

Whatever gets past

WHERE Column1 = @var1 AND Column2 = @var2

is not going to get past

WHEN Column1 = @var1 AND Column2 = @var2 THEN 'B'

is it? Thus nothing will be Rank C ... Maybe I'm missing something?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources