Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Closest companies
#MySQL 5.7.12 #please drop objects you've created at the end of the script #or check for their existance before creating #'\\' is a delimiter DROP TABLE IF EXISTS geocodes ; DROP TABLE IF EXISTS company_has_address ; CREATE TABLE geocodes ( address_id integer not null primary key, geocodes_latitude float, geocodes_longitude float ) ; CREATE TABLE company_has_address ( companys_id integer not null, address_id integer not null references geocodes(address_id) ) ; INSERT INTO geocodes VALUES (1, 33.20, -86.78), (2, 33.20, -86.79), (3, 33.21, -86.80), (4, 33.19, -86.80), (5, 33.19, -86.80) ; INSERT INTO company_has_address VALUES (1, 1), (2, 2), (2, 3), (2, 4), (1, 5) ; SELECT companys_id, min(distance) AS distance FROM ( SELECT company_has_address.companys_id, (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance FROM geocodes LEFT JOIN company_has_address ON company_has_address.address_id = geocodes.address_id WHERE company_has_address.companys_id != 5884 ) AS s0 GROUP BY companys_id ORDER BY distance ASC LIMIT 50 ; DROP TABLE company_has_address ; DROP TABLE geocodes ;
run
|
edit
|
history
|
help
0
OK
compile visitors list for last 8 days
mysql - insert
Mysql join same table where not in
proj
practicee
Estudiantes
poi
air_flight
test