SQL query with multiple ORDER BY Field statements - or perhaps a new approach?

Question

I'm attempting to build a query that finds companies that are within a proximity/distance from a zip code.

Right now a user enters a zip code (via web form) and a radius (1, 5, 10 miles, etc) and I'm using this PHP class to find the closest zip codes, radius, distance, etc (which is working great). Then I have a query that finds all companies which have these zip codes and lists them out.

The issue I have is that the zip codes are stored in three places in the database. They are either stored in the companies table, and/or one of two other related tables (simplified example below). And I can't figure out how to ORDER the results by the proximity regardless of where the zip code field is.

Tables:

company (table) id, name, zip

company_delivery (table) id, company_id (fk), zip

company_other_areas (table) id, company_id (fk), zip

$zip_codes is an array of zips outputted from the zip code class noted above where they are listed closest to furthest:

$zip_code_array = array(
    90210,
    90211,
    90212
)

//* edited - thanks @crafter
$zip_codes = implode(', ', $zip_code_array);

The Query I'm working with

$sql="
SELECT
  company.id, company.name, company.zip, company_delivery.zip, company_other_areas.zip
FROM
  company
LEFT JOIN
  company_delivery ON company.id = company_delivery.company_id
LEFT JOIN
company_other_areas ON company.id = company_other_areas.company_id
WHERE
  company.zip IN($zip_codes) OR company_delivery.zip IN($zip_codes) OR company_other_areas.zip IN($zip_codes)
GROUP BY
  company.id
ORDER BY
  FIELD(company.zip, $zip_codes),
  FIELD(company_delivery.postal_code, $zip_codes)
  FIELD(company_other_areas.postal_code, $zip_codes)
";

I can't seem to figure out to order the companies by zip code, in the same order they are returned from the Zip Code class. - With the above query it orders by each "FIELD" independently. Is it possible to put all the returned zips codes from the database into a merged variable that can be ordered by?

Thanks for any help or pointers on this!

You can merge the tables using UNION http://stackoverflow.com/questions/3531251/using-union-and-order-by-clause-in-mysql — Mohsin Inayat Khan, Apr 13 '14 at 21:33
Thanks! - This seems like a good idea, however the three zip tables all have equal priority. Or, perhaps I'm just not understanding? — user2682676, Apr 13 '14 at 22:18

score 0 · Answer 1 · answered Apr 13 '14 at 19:34

0

Replace

FIELD(company.zip, $zip_codes),
FIELD(company_delivery.postal_code, $zip_codes)
FIELD(company_other_areas.postal_code, $zip_codes)

with

COALESCE(
    FIELD(company.zip, $zip_codes),
    FIELD(company_delivery.postal_code, $zip_codes)
    FIELD(company_other_areas.postal_code, $zip_codes)
)

Should do the trick.

answered Apr 13 '14 at 19:34

mzedeler

4,177
4
28
41

Thanks for the response! I attempted to use COALESCE as you had suggested but the results are still somewhat random. (ie, Companies with zip 90212 listed before companies with zip 90210). Is there a way to merge the three tables results into a new temp variable to ORDER by? thanks again! – user2682676 Apr 13 '14 at 20:12
All that `COALESCE` does is choosing the first non-`NULL`-value among the provided values. Is there something else, you need? – mzedeler Apr 15 '14 at 15:21

score 0 · Answer 2 · answered Apr 13 '14 at 22:31

I'd COALESCE the fields in the SELECT clause, then order by column number in the order by clause. Like this:

SELECT company.id, 
       company.name, 
       COALESCE(company.zip, company_delivery.zip, company_other_areas.zip) AS zip_code
  FROM company
  LEFT JOIN company_delivery ON company.id = company_delivery.company_id
  LEFT JOIN company_other_areas ON company.id = company_other_areas.company_id
 WHERE (   company.zip IN($zip_codes) 
        OR company_delivery.zip IN($zip_codes) 
        OR company_other_areas.zip IN($zip_codes)
       )
 GROUP BY company.id
 ORDER BY 3
;

score 0 · Answer 3 · answered Apr 13 '14 at 22:36

0

Using union, a temporary table will be created which will have records from three queries and order by clause will sort the records regardless of the sequence of queries.

answered Apr 13 '14 at 22:36

Mohsin Inayat Khan

1,101
1
8
10

Could you give me an example of what the query would look like using UNION w/ related tables? MySQL is complaining that the tables don't have/match the correct number of columns. – user2682676 Apr 13 '14 at 22:59

score 0 · Answer 4 · answered Apr 14 '14 at 14:28

It should be something like that

SELECT company.name, a.company_id, a.zip FROM ( SELECT id AS company_id, zip FROM company UNION ALL SELECT company.id, zip FROM company_delivery  UNION ALL SELECT company.id, zip FROM company_other_areas ) a JOIN company ON a.company_id = company.id ORDER BY a.zip;

SQL query with multiple ORDER BY Field statements - or perhaps a new approach?

4 Answers4