O Connor - 2 years ago 69
SQL Question

# Select records based on the postal code and it's radius in MySQL

Below is my

`postal`
table with a few records. In fact this table contains all cities and all postal codes.

``````id    city                 postalcode    latitude          longitude
1    Drogteropslagen      7705 PA       52.61666700       6.50000000
2    Coevorden            7740 AA       52.66666700       6.75000000
3    Emmen                7812 TN       52.78333300       6.90000000
4    Emmer-Compascuum     7881 PZ       52.81666700       7.05000000
5    Nieuw-Dordrecht      7885 AA       52.75000000       6.96666700
``````

Below is my
`company`
table with it's postal code and the radius in kilometers where each company is able to provide his services.

``````id   company_name   city              postalcode   radius   latitude      longitude
1    A              Emmen             7812 TN      10       52.78333300   6.90000000
2    B              Nieuw-Dordrecht   7885 AA      5        52.75000000   6.96666700
3    C              Drogteropslagen   7705 PA      25       52.61666700   6.50000000
4    D              Foxhol            9607 PR      0        53.16666700   6.71666700
5    E              Westerbroek       9608 PA      15       53.18333300   6.68333300
``````

I would like to select the companies which a particular postal code e.g.
`7740 AA`
lives in the area of the postal code plus the radius of a company. Note that the particular postal code might not always exist in the
`company`
table but always exist in the
`postal`
table. How to write a sql query to select those companies.

This approach performs a `CROSS JOIN` between a subquery containing a single record for the company of interest against a subquery containing every company. The query uses the Haversine formula for computing the distance between two companies in kilometers (and you can read more about that here).

This query is what you would use to get all companies within a 20km radius of postal code `7740 AA`.

``````SELECT t2.company_name,
FROM
(
SELECT p.latitude AS lat1, p.longitude AS lng1
FROM postal p
WHERE p.postalcode = '7740 AA'
) t1
CROSS JOIN
(
SELECT c.company_name, p.latitude AS lat2, p.longitude AS lng2
FROM company c INNER JOIN postal p
ON c.postalcode = p.postalcode
) t2
HAVING distance < 20
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download