MySQL subquery in select, return multiple columns

Question

I am working with 2 tables. location and us_zipcodes.

location contains user coordinates and travel speed.

us_zipcodes contains all cities, their zipcodes, and coordinates in the US.

The goal here is to return the users latitude, longitude, speed, and nearest city.

In my select, I am trying to return 2 columns from us_zipcodes in a select subquery. I understand one might recommend a join, but my circumstances do not allow an ON.

The subquery selects the city based on the users coordinates that are closest to the city's coordinates in the us_zipcodes table.

I am looking to know if what I want to do is possible, and if so, the correct syntax.

My attempt is below. Thank you in advance.

SELECT
    l.lat,
    l.lng,
    l.speed
    (
        SELECT
            CONCAT(city, ' ', state_abrv) AS nearest, 
            (
                3959 * 
                acos( 
                    cos( radians( l.lat ) ) * 
                    cos( radians( lat ) ) * 
                    cos( 
                        radians( lng ) - 
                        radians( l.lng ) 
                    ) + 
                    sin( radians( l.lat ) ) * 
                    sin( radians( lat ) ) 
                ) 
            ) AS distance
        FROM
            us_zipcodes
        ORDER BY
            distance ASC
        LIMIT 0, 1
    ) AS nearest, distance
FROM
    location AS l
WHERE
    l.userID = :userID

EDIT: My table structure is as shown.

TABLE: location

ID      |       lat         |       lng         |       speed       |       userID  
___________________________________________________________________________________
1       |       55.159399   |       -74.98976   |       35          |       1       
2       |       45.168399   |       -52.56476   |       45          |       2       
3       |       64.593399   |       -64.32576   |       55          |       3       
4       |       98.193399   |       -72.81176   |       65          |       4

TABLE: us_zipcodes

ID      |       city        |       state_abrv      |       lat     |       lng
______________________________________________________________________________________
1       |       Foo City    |       MI              |       45.3265 |       -81.98747
2       |       Bar City    |       AK              |       65.3265 |       -65.98747
3       |       Fake City   |       FL              |       25.3265 |       -75.98747
4       |       Smith City  |       MI              |       64.3265 |       -89.98747

Because there is no sharing columns in the two tables. There is no link. The selection in the inner join is based on the nearest coordinates compared to the lat and lng of the outer query. — Adam Joseph Looze
– Adam Joseph Looze, Commented Oct 24, 2016 at 13:29

Vadym Kokin · Accepted Answer · 2016-10-24 18:08:26Z

Maybe something like this can help

SELECT
  state_abrv,
  city,
  3959 * acos(
      cos( radians( (SELECT lat FROM location WHERE userID = :userID) ) ) *
      cos( radians( lat ) ) *
      cos(
          radians( lng ) -
          radians( (SELECT lng FROM location WHERE userID = :userID) )
      ) +
      sin( radians( (SELECT lat FROM location WHERE userID = :userID) )) *
      sin( radians( lat ) )
  ) AS distanse,
  (SELECT lng FROM location WHERE userID = :userID) as lng,
  (SELECT lat FROM location WHERE userID = :userID) as lat,
  (SELECT speed FROM location WHERE userID = :userID) as speed

FROM us_zipcodes ORDER BY distanse LIMIT 1;

or just two separate subqueries

SELECT
  l.lat,
  l.lng,
  l.speed,
  (SELECT (3959 * acos(
                cos(radians(l.lat)) *
                cos(radians(lat)) *
                cos(radians(lng)-radians(l.lng)) +
                sin(radians(l.lat)) *
                sin(radians(lat)))
  ) 
   FROM us_zipcodes
   ORDER BY distance ASC
   LIMIT 0, 1
  ) AS distance,
  (SELECT CONCAT(city, ' ', state_abrv)
   FROM us_zipcodes
   WHERE 3959 *
         acos(cos(radians(l.lat)) * cos(radians(lat)) *
             cos( radians(lng) - radians(l.lng)) +
             sin(radians(l.lat)) *
             sin(radians(lat))
         ) = distance)  AS nearest
FROM
  location AS l
WHERE
  l.userID = :userID;

Collectives™ on Stack Overflow

MySQL subquery in select, return multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related