>I`m trying to find the best method to match records between two tables in a
>MySQL database. But it must match atleast once, so here is a scenario for you
>to think about.
>
>Member - Job, Salary, Location
>Jobs - Job, Salary, Location
>
>All values will be enum sets in both tables as follows
>
>Job = enum 'developer','designer','none'
>Salary = enum '10K','15K','none'
>Location = enum 'usa','uk','none'
>
>Ok so the user has come to the site and I have his Job Salary and Location
>stored, he clicks a link which then performs the search. His values are as
>follows..
>
>Job = developer, Salary = 10K, Location = uk
>
>I want to search the Jobs table and match it with the above, this part I can
>do. The next stage is if it doesn`t find any matches it then finds the
>nearest. So if the only record I have in the Jobs table is as follows
>
>Job = developer, Salary = none, Location = uk
>
>How do I go about making it pick up the nearest.
>
>At the moment I am using
>
>SELECT * FROM Jobs where Job='$Job' and Salary='$Salary' and
>Location='$Location'
>
>So obviously it won`t match them, anyone wake me up from my slumber and help
>me out with this?
>
>Cheers
>Ade
Sir, the following, or some variation, should get you the closest
matches on salary. You can modify or remove the LIMIT clause to get
various numbers of results.
SELECT *, Abs('$Salery' - Salery) AS delta
FROM Jobs
WHERE Job='$Job' AND Location='$Location'
ORDER BY delta LIMIT 1;
For location, you'll have to have some sort of lookup system based
either on longitude and latitude or the British postal codes. The
easiest solution is to let the user handle it; the user does one
search for each location the user thinks is close enough to the ideal
location.
The problem of finding a job title that is close to another job title
is a problem outside of database theory. The US government uses a
numeric coding system for jobs. If the UK government does something
similar, you might be able to use that to make rough comparisons of
job titles. Even if you can find an existing solution that you can
adapt to your needs, you've still got your work cut out for you. An
easier solution might be to allow the user to view the available job
titles (SELECT DISTINCT Job ..., or have a predetermined list of
jobs) and then have your code build a query based on the jobs
selected by the user.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
