May 21, 2005 at 10:42 am
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
May 24, 2005 at 9:43 am
Please see my answer in this thread http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=183974 which is a duplicate.
May 24, 2005 at 9:52 am
Thank you ron k, your solution worked brilliantly!
May 24, 2005 at 10:18 am
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
May 24, 2005 at 10:51 am
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.
May 24, 2005 at 11:37 am
You data probably has multiple rows that have the same counts of "OP." change the "=" sign to "IN".
Thanks
Greg
May 24, 2005 at 11:53 am
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!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply