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

Answer 1

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;