Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scripter
Starting Member
49 Posts |
 Posted - 2011-11-17 : 23:38:22
|
| Ok I am building myself a dating site to teach myself some new/old ideasOn a dating site you have a search feature that you have to select all of the features you are looking for in a person.Well now that I am actually thinking of how this must work I realize I really don't understand it at all.Well I believe I theoretically understand it. If I select we will say 5 options If I do a simple select statement with the WHERE simply states all of the field/values I want than I may miss out on someone that could be a good match for me but may not meet specifically each criteria So what I gather is I need to be able to put in a select statement that will somehow count all of the matches that meet my selection and than orders those results by the most matches.So how would I do something like that?If anyone knows of a good site or anything I can look at and figure this out or heck if you have a script that does this than awesome |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-18 : 01:35:58
|
| it should be a list of optional filters you should use for searching. it will try to find the matches based on filters and will rank them based on number of filters it matched. the one with most matches will be listed first and one with least matches listed last. You need to check if at least one of this is matching before you return it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
scripter
Starting Member
49 Posts |
Posted - 2011-11-18 : 01:51:03
|
| Do you by anychance have something you can show me on this. just something generic I am sure I will be able to understand it I just don't see how to do it as it stands so far.I mean if you do a statement likeSELECT UserName,Email,City, State FROM Profile WHERE City='Clearwater' AND State='FL'Then this will only return Items with both City='Clearwater' AND State='FL'If I Do OR statement instead I could get a more Legit response but then we are back to how do you apply a count to it for this. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-18 : 02:05:06
|
| [code]SELECT columns...FROM (SELECT UserName,Email,City, State,CASE WHEN City='Clearwater' THEN 1 ELSE 0 END + CASE WHEN State='FL' THEN 1 ELSE 0 END +... AS MatchWeightage FROM Profile WHERE City='Clearwater' OR State='FL'...)tORDER BY MatchWeightage DESC[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
scripter
Starting Member
49 Posts |
Posted - 2011-11-18 : 02:26:19
|
| Ok I going to check that out in the morning since i cannot keep my eyes open.Thank you |
|
|
|
scripter
Starting Member
49 Posts |
Posted - 2011-11-18 : 11:37:04
|
| I tested it out and looks like it is working good. I do have a question about this though is there any way to make it more efficient?I found a program that fills the database with test data so I put 20000 records in the table to see how it would perform.It takes a long time to search. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-18 : 11:40:41
|
quote:
Originally posted by scripter
I tested it out and looks like it is working good. I do have a question about this though is there any way to make it more efficient?I found a program that fills the database with test data so I put 20000 records in the table to see how it would perform.It takes a long time to search.
how many search criteria you need to check?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
scripter
Starting Member
49 Posts |
Posted - 2011-11-18 : 12:28:53
|
| Approximately 23 or so |
|
|
|
|
|
|
Search Profiles