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 <mysql-thread67425@xxxxxxxxxxxxxxx> To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php