On Saturday 07 June 2008, Kristina Anderson wrote: > I have three tables, an Employer table, a Provider table and a Jobs > table. Each Provider (worker) has up to nine zip codes and a possible > metro area which contains all the zip codes in their city which is > their "service area". > > When they log on, I need to display only the Jobs in any of their zip > codes.
The easiest thing would be to always populate the zip code in the Jobs table. Don't call it "remote zip", call it "job zip" and populate it when you create the job whether it's the same as the employer's zip or not. SELECT * From Jobs INNER JOIN Employees ON (Employees.EmployeeID = $EmployeeID) AND ((Employees.zip1 = Jobs.jobzip) OR (Employees.zip2 = Jobs.jobzip) OR (Employees.zip3 = Jobs.jobzip)) or let's say you want to show the employer too... SELECT * FROM Jobs INNER JOIN Employees ON ((Employees.EmployeeID = $EmployeeID) AND ((Employees.zip1 = Jobs.jobzip) OR (Employees.zip2 = Jobs.jobzip) OR (Employees.zip3 = Jobs.jobzip))) LEFT JOIN Employers ON (Jobs.EmpID = Employers.EmpID) Now, you can still do it in one query even if you don't change the job zip code as I suggested above - you'll have to sub-select the zip from the employer table, left as an exercise for the reader... As a general rule, if you can make the database do your mix and matching, I prefer to do it there rather than in arrays. In 99% percent of cases the database will happily give you exactly what you need if you just ask it nicely. Michael Sims _______________________________________________ New York PHP Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
