2

Good evening all, first post on the DBA forums. I am having real issues working out how to accomplish a seemingly simple task within MySQL as a model for PHP code later on. I am not exactly a MySQL noob, but definitely not an expert either. :) Here's the scenario.

There are 2 tables primarily involved here. The first (tblZipCodeServed) has 3 columns:

  • zipcodeservedID (auto increment field for unique ID)
  • zipcodeservedStaffID (staff memeber ID)
  • zipcodeservedZipCode (actual ZIP being served)

The second table (tblZipCodeDist) has 4 columns:

  • zipcodedistID (auto increment field for unique ID)
  • zipcodedistStartZip (starting ZIP code for distance measurement)
  • zipcodedistEndZip (ending ZIP code for distance measurement)
  • zipcodedistMiles (miles between the 2 zip codes)

So the ultimate objective here is to figure out which staff member currently serves the ZIP Code closest to that of a new enrollee, to help the coordinators figure out who should be assigned to the new enrollee. Each staff member serves multiple ZIP codes, so I need to get the miles between each ZIP code they currently service and the target ZIP code... but only display/return/whatever the smallest number of miles. This data would need to be added as a column in a currently existing query to show assorted other info that assists the coordinators in their decision making. Here's a totally fictitious example to show what I mean:

New enrollee Tom B lives in ZIP code 12345. Staff member David Z currently serves 4 zip codes, the mileage from 12345 being: 4.6, 5.5, 8.7, and 12.0 miles. Staff member Anna T currently serves 5 ZIP codes, the mileage from 12345 being: 2.4, 4.1, 12.8, 16.2, and 21.0 miles. Staff member Dawn J currently serves 3 ZIP codes, the mileage from 12345 being: 0.2, 1.6, and 7.7 miles. When coordinator Janie C adds Tom B to the system and clicks the button to help her decide the staff member assignment, she should see something like the following:

+-----------------------------------------------+
| Staff Name | {assorted cols} | Miles From ZIP |
|------------+-----------------+----------------|
| Dawn J     | {assorted data} | 0.2            |
| Anna T     | {assorted data) | 2.4            |
| David Z    | {assorted data) | 4.6            |
+-----------------------------------------------+

I have tried straight-up select queries, joins, unions, subqueries - even dabbled in stored procedures (though I had no idea what I was doing) - all of which were subsequentally deleted as they didn't work and I am fairly annoyed about the whole thing. I know the process I need to execute to get what I want out of the DB, I just don't know how to get there. The process, in my head, is as follows:

  1. Get all StaffIDs and Zip Codes Served (from tblZipCodeServed), one staffID at a time
  2. For each staffID, loop through all Zip Codes Served, looking up the distance between it and the target zip in tblZipCodeDist to get miles.
  3. Print/Return/Capture lowest mileage amount
  4. Move on to next staffID
  5. Repeat 2-4 for each staffID
  6. Print lowest mileage as additional column in existing report.

Can someone point me in the right direction? I am not necessarily looking for anyone to code/author/create this for me (though if it's quick and easy for you experts, and you wouldn't mind, I certainly wouldn't be mad)... I just need some pointers as I am apparently missing some key concept here. Maybe there's a MySQL tool/function I don't know about? Maybe I am just not up to this ninja level as of yet? Whatever the issue is here, I would greatly appreciate any and all help you can provide.

Thank you very much, in advance!!

EDIT - My Solution Thanks to ypercube for the answer... here's what I came up with (all fields listed, not just the snippet) that seems to do the trick (in PHP, but the same concept... $pzc is a stand-in for "ParentZipCode", while $cpl is a stand-in for "ChildPriLang" - both of which are fed in via web form):

$getrecquery = "select DISTINCT a.staffID, concat(a.staffFName, \" \", a.staffLName) as HVNAME, a.staffBaseZip, c.zipcodedistStartZip, c.zipcodedistEndZip, "
    . "c.zipcodedistMiles, a.staffCaseLoadMax-(select count(*) from tblRoster d where d.rosterStaffID = a.staffID) as CasesLeft, "
    . "(select min(c.zipcodedistMiles) from tblStaffZipServed b, tblZipCodeDist c "
    . "where ((c.zipcodedistStartZip = b.staffzipservedZipCode) OR (c.zipcodedistEndZip = b.staffzipservedZipCode)) AND "
    . "((c.zipcodedistStartZip = $pzc) OR (c.zipcodedistEndZip = $pzc)) AND b.staffzipservedStaffID = a.staffID) as MinMiles "
    . "from tblStaff a, tblStaffZipServed b, tblZipCodeDist c, tblRoster d "
    . "where a.staffID = b.staffzipservedStaffID and "
    . "((c.zipcodedistStartZip = $pzc) and (c.zipcodedistEndZip = a.staffBaseZip)) or "
    . "((c.zipcodedistEndZip = $pzc) and (c.zipcodedistStartZip = a.staffBaseZip)) and "
    . "(a.staffPriLang = $cpl or a.staffAltLang = $cpl) and "
    . "a.staffCaseLoadMax-(select count(*) from tblRoster d where d.rosterStaffID = a.staffID) > 0 "
    . "order by MinMiles, c.zipcodedistMiles, CasesLeft, HVNAME;";

This query outputs exactly what I need. The key for me was using the subquery as a column and getting that to work properly, rearranging pieces of it's where clause until I got the desired output.

Hopefully this info can help someone else out in the future!!

END EDIT

2

Assuming a Staff table, it would be a query similar to this:

SELECT 
    s.*,                         -- needed columns from Staff table
    MIN(d.zipcodedistMiles) AS Miles_From_ZIP
FROM 
    Staff AS s
  LEFT JOIN
    tblZipCodeServed AS z
      ON  z.zipcodeservedStaffID = s.StaffID
  LEFT JOIN
    tblZipCodeDist AS d
      ON  d.zipcodedistStartZip = z.zipcodeservedZipCode
      AND d.zipcodedistEndZip = @TargetZipCode
GROUP BY
    s.StaffID ;                  -- the Primary Key of Staff
  • Thank you very much for your response, ypercube! I appreciate it greatly. I will edit my post with the query I came up with... but you certainly earned points in my book here as you were on the same thought track I was. :) – TheJester1977 Jan 13 '15 at 7:25

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.