January 18, 2008 at 1:48 pm
I'm at a logical brick wall.
I have 3 search methods. Each is fairly involved so they are implemented as functions. Each returns a table with the same shape comprised of the same type of objects. My problem is that I need to be able to combine multiple search results like in the pseudo-code below:
function fCombinedSearch( @type1Criteria, @type2Criteria, @type3Criteria )
(
select * into #A from fSearch1(@type1Criteria)
select * into #B from fSearch2(@type2Criteria)
select * into #C from fSearch3(@type3Criteria)
select #A.key into #X from #A join #B on #A.key = #B.key join #C on #A.key = #C.key
return (select * from #A where key in (select key from #X)
union select * from #B where key in (select key from #X)
union select * from #C where key in (select key from #X))
)
So, the combined search returns the intersection of all three searches. I'm stuck because each search method is optional. So, the user may only wish to search using type1 and type3. If so, #B (the results of search3) has to be excluded from the intersection.
Maybe I'm just burned out this week, but for the life of me I can't figure out how to do this (except for if blocks that would fall apart if a 4th search method is added).
January 19, 2008 at 12:05 pm
How do you know when your parameter contains an optional value? Lets assume for a minute that it is NULL. You could do something like:
If @Parm1 is NULL
Insert INTO #tempa
Select * From valtable
Else
select * into #A from fSearch1(@type1Criteria)
Then you could continue with the rest of your logic. Even though it would be inefficient if your valTable is large.
Another option is perhaps
select #A.key into #X from
#A join #B on #A.key = #B.key or @Parm1 IS NULL
join #C on #A.key = #C.key or @Parm2 IS NULL
Not sure of performance implications in either case, but perhaps this triggers a gray cell.
January 21, 2008 at 7:24 am
selecting all values is not an option since there about 36,000 items currently in 6 months of data and we expect 120K items or more in the full 5 year database. i got a solution based on the logic below working this weekend. it's quick and easily extended to support additional search types.
alter procedure CombinedSearch_sp( @type1Criteria varchar(1000),
@type2Criteria varchar(1000), @type3Criteria varchar(1000), @maxResults int = 100 )
with recompile
as
begin
declare @setQty int
set @setQty = 3 -- adjust if more search types added
select * into #a from fSearch1( @type1Criteria)
if (@@rowcount = 0 and len(@type1Criteria) > 2)
begin -- if no match and criteria supplied, return with empty result set
select * from #a
return
end
select key, 1 as setNum into #keys from #a
select * into #b from fSearch2( @type2Criteria)
if (@@rowcount = 0 and len(@type2Criteria) > 2)
begin
select * from #b
return
end
insert into #keys select key, 2 as setNum from #b
select * into #c from fSearch3( @type3Criteria)
if (@@rowcount = 0 and len(@type3Criteria) > 2)
begin
select * from #c
return
end
insert into #keys select key, 3 as setNum from #c
if not exists (select 1 from #a) set @setQty = @setQty - 1
if not exists (select 1 from #b) set @setQty = @setQty - 1
if not exists (select 1 from #c) set @setQty = @setQty - 1
-- get keys which appear in all populated results sets
select key, count(distinct setNum) as setQty
into #k
from #keys
group by key
having count(distinct setNum) = @setQty
select #a.*
into #z
from #k join #a on #a.key= #k.key
union
select #b.*
from #k join #b on #b.key = #k.key
union
select #c.*
from #k join #c on #c.key = #k.key
select top (@maxResults) * from #z order by [order_by_columns ...]
end
January 23, 2008 at 11:10 am
to Jeremy:
we rolled out the combined search to testers yesterday. they liked it so much that they've asked for a 4th search method! so the technique described earlier is working very well. 🙂
January 23, 2008 at 4:17 pm
10x
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply