Best explaination Order by Match Count

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Best explaination Order by Match Count

Author

Topic

scripter
Starting Member

49 Posts

Posted - 2009-04-07 : 11:18:29

Ok I have a client that I am building a site for it is for the sake of long explaination a dating site or i guess better description a job site.

essentially you have the employees then you have employers.
when both signup they have to fill out their profiles. the employers have to state what they want from the employee and it matches up to the items the employee entered at signup. pretty standard stuff.

Well I have been looking all around on the net for this and cannot find anything on it or for that matter not even sure if their are better phrases to explain what I am looking for.

EmployeeTable | EmployerTable
PreferredEmployerState | EmployerState
ClassificationID | ClassificationID
OfficeSpace | OfficeSpace
GroupHealth | GroupHealth

Essentially what I am looking for is a query that will say if employee A matches 4 out of 4 their name shows up first in the list of prospects.
Employee B Matches 3 out of 4 their name shows up second in the list
and so on.

Any help you can be on this would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:23:48

which is field that stores the employer preferences?

scripter
Starting Member

49 Posts

Posted - 2009-04-07 : 11:40:31

EmployeeTable | EmployerTable
PreferredEmployerState | EmployerState
ClassificationID | ClassificationID
OfficeSpace | OfficeSpace
GroupHealth | GroupHealth

This diagram poor as it may be is showing the tables and the fields the two tables EmployeeTable | EmployerTable
then each item below that is the fields for those tables side byside matching up with eachother
this is not all of the fields but I am not looking for someone to write the entire sql for me I just need help gettting put into the right direction on this.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-07 : 13:01:35

Perhaps something like this:


select ee.Name
       ,case when ee.PreferredEmployeeState = er.EmployerState then 1 else 0 end
       + case when ee.ClassificationID = er.ClassificationID then 1 else 0 end
       + case when ee.OfficeSpace = er.OfficeSpace then 1 else 0 end
       + case when ee.GroupHealth = er.GroupHealth then 1 else 0 end
       as [matchCount]
from   EmployeeTable ee
inner join EmployerTable er on er.name = '<Employer to get a list of prospects for>'
order by 2 desc

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources