Crazy query help

Question

Crazy query help

dcross

SSC Enthusiast

Points: 133
More actions
May 21, 2005 at 10:42 am

#87558

I have a query that was provided to me by member ~Ananda Kumar which is almost working correctly, but I need some help refining it.
The query should be kind of a recursive thing. Basically, first it should look for matches on all parameters as they are given (Op1 through Op5 in the code below), 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).
Here's a sample of data from my Results table:
ResultID Link           Description Op1 Op2    Op3    Op4    Op5
-------------------------------------------------------------------
101      http://thoth   this site   106 111    116    122    134
112      mailto:dcross some guy    100 <NULL> <NULL> <NULL> <NULL>
113      mailto:me      mememememe 100 107    112    120    <NULL>
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 * 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 two results:
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    <NULL>
What I actually need returned is this single result (since this result has the most 'matches' on the parameters given -- Op1 through Op4):
ResultID Link           Description Op1 Op2    Op3    Op4    Op5
-------------------------------------------------------------------
113      mailto:me      mememememe 100 107    112    120    <NULL>
I'm sure this is clear as mud, but I'm hoping someone can wade through it...

Any help you can provide is greatly appreciated, as this is a very time-sensitive issue.
Thanks,
-DC Ross
May 24, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

ron k SSCertifiable Points: 6103 More actions · Answer 1

Please see my answer in this thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=183974 which is a duplicate.

dcross SSC Enthusiast Points: 133 More actions · Answer 2

Thank you ron k, your solution worked brilliantly!

Greg Grow SSCrazy Points: 2386 More actions · Answer 3

This is just another alternative. This will count the columns used and pulls out the one that has the most columns used. More of a straight sql approach.

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 r.ResultID, Link, Descr, op1, op2, op3, op4, op5

FROM Result r

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

AND r.resultid = (select resultid

from (SELECT resultid,

SUM(

convert(int, convert(bit, isnull(op1, 0))) +

convert(int, convert(bit, isnull(op2, 0))) +

convert(int, convert(bit, isnull(op3, 0))) +

convert(int, convert(bit, isnull(op4, 0))) +

convert(int, convert(bit, isnull(op5, 0)))) as Max_Result

FROM Result

GROUP BY resultid) as t

where t.max_result = (select max(max_result)

from (SELECT resultid,

SUM(

convert(int, convert(bit, isnull(op1, 0))) +

convert(int, convert(bit, isnull(op2, 0))) +

convert(int, convert(bit, isnull(op3, 0))) +

convert(int, convert(bit, isnull(op4, 0))) +

convert(int, convert(bit, isnull(op5, 0)))) as Max_Result

FROM Result

GROUP BY resultid) as t1))

Thanks

Greg

dcross SSC Enthusiast Points: 133 More actions · Answer 4

Thanks Greg, I like the straightforward approach, but I'm getting a "Subquery returned more than 1 value" error when attempting to run your query. The first subquery (starting on line 18) is returning four results.

Greg Grow SSCrazy Points: 2386 More actions · Answer 5

You data probably has multiple rows that have the same counts of "OP." change the "=" sign to "IN".

Thanks

Greg

dcross SSC Enthusiast Points: 133 More actions · Answer 6

Thanks again, Greg, that seemed to work at first glance, but if you replace the value of @Op5 with null, no results are returned, where ResultID 116 in the above example should be returned.

Thanks again! I really appreciate your time and help!