Link to home
Start Free TrialLog in
Avatar of EdwardPeter
EdwardPeter

asked on

match making (complex)

Hi,

How can we find set of records base on the given set of scores per category?

Please kindly help me create a store procedure that will return the Table_score.scoreID if found and return 0 if not.

Thanks.

i.e.

User gave Peter score's:
singing = 89                 <-------------------set of scores given by user
dancing = 78
eating = 90

table_category                                         <--containing categories criteria
MainCatID    SubCatID    SubDescription  
     1                  1              singing
     1                  2              dancing
     2                  1                eating      

Table_score
scoreID   MainCatID         SubCatID    score
1                1                       1             89   <---this is not a match has 4 categories (sing, dance, eat, other one unknown)
1                1                       2             78
1                2                       1             90
1                1                       3             10

2                1                       1             89                    <-----------we found a match singing   -l
2                1                       2             78                    <-----------we found a match dancing  -l--perfect set match 3 cat
2                2                       1             90                    <-----------we found a match eating     -l

3                1                       1             89 <---this is not a match since it has 2 categories (singing, dancing)
3                1                       2             78                    



return scoreID -which is 2
else
return 0

*one condition scoreID should be the same for the set of scores.
Table_score
scoreID   MainCatID         SubCatID    score
2                1                       1             89                    <----------2
2                1                       2             78                    <----------2
2                2                       1             90                    <----------2



Thanks.
Avatar of xnmb
xnmb

would the input parameters for the SP be a set of IDs of categories? and find the scoreid that matches these set of IDs?
ASKER CERTIFIED SOLUTION
Avatar of BillAn1
BillAn1

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EdwardPeter

ASKER

BillAn1,

Amazing, it took my breath away!

Yes, that is correct flexibility is the key, specially the part where user will add/delete categories.

table_category                                      
MainCatID    SubCatID    SubDescription  
     1                  1              singing
     1                  2              dancing
     2                  1                eating      

When user add's one we will need to modify the Stored Function, is there anyway we can lookup the number of records in table_categories instead of manually editing the stored function everytime there's going to be a change in the table?

Thanks.
actually, strictly speaking hte function does not care about the #records in the table_category table. ALl it cares about is the number of parameters passed in, so if you call the function with 3 parameters (i.e. 3 sets of paramters which are not NULL), then it will check that there are 3 matching records in the table_score table, if you call it with 4 it will match for 4 etc. If you know that the maximum # ever will be say 5, you could create the function with 5 sets of parameters, and then just call it with the required # of not-null parameters.
If you want more flexibility, you could call it with a single comma-delimited parameter, such as :

alter function GetScoreID (@parameters varchar(1000))
returns integer
as
begin

     declare @sc table (descr varchar(32), score integer)
     declare @descr varchar(32)
     declare @score integer

     declare @scoreID integer

     while @parameters <> ''
     begin
        set @descr = left(@parameters, charindex(',',@parameters)-1)
        set @parameters = right(@parameters, len(@parameters) -  charindex(',',@parameters))
      if charindex(',',@parameters) > 0
      begin
              set @score = cast(left(@parameters, charindex(',',@parameters)-1) as integer)
              set @parameters = right(@parameters, len(@parameters) -  charindex(',',@parameters))
      end
      else
      begin
            set @score = cast(@parameters as integer)
            set @parameters = ''
      end
      insert into @sc values (@descr, @score)
     end    

     select @scoreID = scoreID
      from @sc sc
     join table_category tc on sc.descr = tc.SubDescription
     join Table_score ts on ts.MainCatID = tc.maincatid and ts.subcatid = tc.subcatid and ts.score = sc.score
     group by scoreID
     having count(*) = (select count(*) from table_score ts2 where ts2.scoreID = ts.scoreID)
     and count(*) = (select count(*) from @sc)

     set @scoreID = isnull(@scoreID,0)    
     
     return @scoreID
end


then you can call this as :
select dbo.GetScoreID2('singing,89,dancing,78,eating,90')
you can just pass as many parameters as you like in this case.
a couple of caveats :
1. you can't have a description with a comma in it, as commas are now used to delimit the list  (e.g. a description of "Hop,skip and jump" would cause the function to fall over)
2. You might want to put some error handling to make sure that the parameter passed in is of the correc tofrmat - e.g. that it contains an even # of parameters, and that each 2nd one is actually a number etc. otherwise the function might crash if it is called incorrectly.
BillAn1,

Your simply the best. You rock my world.

Thanks.