how to return one result set from multiple queries (ie. no results from query1, go to query2)

Question

Post reply

how to return one result set from multiple queries (ie. no results from query1, go to query2)

dcross

SSC Enthusiast

Points: 133
More actions
May 19, 2005 at 7:26 pm

#93053

I have an unusual query that I'm having trouble with. I have a query with five parameters, that needs to run until a result set is found. I'm not sure of the best way to explain it, so hopefully the code is clear enough to interpret...
--1st QUERY
SELECT ResultID, Link, Description FROM results
WHERE Op1 = @Op1
AND Op2 = @Op2
AND Op3 = @Op3
AND Op4 = @Op4
AND Op5 = @Op5
IF @@ROWCOUNT = 0
BEGIN
--IF NO RESULTS FROM 1st QUERY...
--2nd QUERY
SELECT ResultID, Link, Description FROM results
WHERE Op1 = @Op1
AND Op2 = @Op2
AND Op3 = @Op3
AND Op4 = @Op4
AND Op5 IS NULL
IF @@ROWCOUNT = 0
BEGIN
  --IF NO RESULTS FROM 1st & 2nd QUERIES...
  --3rd QUERY
  SELECT ResultID, Link, Description FROM results
  WHERE Op1 = @Op1
  AND Op2 = @Op2
  AND Op3 = @Op3
  AND Op4 IS NULL
  AND Op5 IS NULL
  IF @@ROWCOUNT = 0
  BEGIN
   --IF NO RESULTS FROM 1st, 2nd & 3rd QUERIES...
   --4th QUERY
   SELECT ResultID, Link, Description FROM results
   WHERE Op1 = @Op1
   AND Op2 = @Op2
   AND Op3 IS NULL
   AND Op4 IS NULL
   AND Op5 IS NULL
   IF @@ROWCOUNT = 0
   BEGIN
    --IF NO RESULTS FROM 1st, 2nd, 3rd & 4th QUERIES...
    --5th QUERY
    SELECT ResultID, Link, Description FROM results
    WHERE Op1 = @Op1
    AND Op2 IS NULL
    AND Op3 IS NULL
    AND Op4 IS NULL
   END
  END
END
END
I'm not sure of the best way to go about reworking this, I am currently getting four empty result sets, plus the one that is not empty. I only want the one that is not empty returned. So, say for example, the fourth query returns results, that's the only one that i want returned.
Any help you can provide is greatly appreciated, as this is a very time-sensitive issue.
Thank you,
DC Ross

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ananda Kumar SSCommitted Points: 1865 More actions · Answer 1

You can write like

SELECT ResultID, Link, Description FROM results

WHERE Isnull(Op1,@op1) = @Op1

AND Isnull(Op2,@op2) = @Op2

AND Isnull(Op3,@op3) = @Op3

AND Isnull(Op4,@op4) = @Op4

AND Isnull(Op5,@op5) = @Op5

did i understand your problem correctly?

dcross SSC Enthusiast Points: 133 More actions · Answer 2

You understood perfectly. Thank you very much, Grasshopper!

-DC Ross (always learning )

dcross SSC Enthusiast Points: 133 More actions · Answer 3

I think I may have spoken too soon. Your solution seemed to have worked initially, but now that I'm working with it, there seems to be a hang-up...

Here's a sample of data from my Results table:

ResultID Link Description Op1 Op2 Op3 Op4 Op5

-------------------------------------------------------------------

112 mailto:dcross some guy 100 <NULL> <NULL> <NULL> <NULL>

113 mailto:me mememememe 100 107 112 120 131

When I run the following query...

declare @Op1 int, @Op2 int, @Op3 int, @Op4 int, @Op5 int

set @Op1 =100

set @Op2 =107

set @Op3 =112

set @Op4 =120

set @Op5 =131

SELECT ResultID, Link, Description FROM Results

WHERE ISNULL(Op1,@Op1) = @Op1

AND ISNULL(Op2,@Op2) = @Op2

AND ISNULL(Op3,@Op3) = @Op3

AND ISNULL(Op4,@Op4) = @Op4

AND ISNULL(Op5,@Op5) = @Op5

I get the following results:

ResultID Link Description

----------- ------------------------------

112 mailto:dcross some guy

113 mailto:me mememememe

What I actually need returned is:

ResultID Link Description

----------- ------------------------------

113 mailto:me mememememe

So, in other words, it's kind of a recursive thing. Basically, first it should look for matches on all parameters as they are given (Op1 through Op5), if nothing is found, look for matches on Op1 through Op4, replacing Op5 with NULL. If still no results are found, look for matches on Op1 through Op3, replacing Op4 and Op5 with NULL, and so on up the line until a match is found (just a note: every possible value for Op1 will return a result).

I'm sure this is clear as mud, but I'm hoping someone can wade through it...

Thanks again,

-DC Ross

ron k SSCertifiable Points: 6103 More actions · Answer 4

Create a temp table and have each of your statements place their results in the temp table. For example Insert into #temp ... Select......

Then at the end do one Select from the temp table to return your results. The first of your nested statements to return any rows will be the only statement to have inserted rows into the temp table.

Edit: A table varible (@temp_table) should also work fine and may be faster.