CODEWITHSUNDEEP
mysql
Ben Harold
Ben Harold

Reputation: 6432

Can I tell MySQL not to return a specific column in the result set?

I have a query that finds locations within a distance of a lat/lng coordinate pair. Here's an example with lat and lng values filled in:

SELECT 
    `id`, 
    ( 3959 * acos( cos( radians('39.8657112') ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians('-86.1478729') ) + sin( radians('39.8657112') ) * sin( radians( `lat` ) ) ) ) AS `distance` 
FROM `locations` 
HAVING `distance` < 0.100000

My problem is that I'd like to use this in a compound query to filter the results of another query. For example:

SELECT * FROM `tickets` WHERE `location_id` IN ( <distance query goes here> );

However, since the distance query is returning two columns (id and distance), the second query fails with a cardinality violation: ERROR 1241 (21000): Operand should contain 1 column(s)

Is there any way that I can tell MySQL to refrain from returning the distance column from the query so that only a list of ids is returned?

Upvotes: 1

Views: 77

Answers (1)

PaulProgrammer
PaulProgrammer

Reputation: 17700

The typical response is to construct a query identical to the distance query, but remove the distance column from the select clause.

SELECT `id` 
FROM `locations` 
WHERE ( 3959 * acos( cos( radians('39.8657112') ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians('-86.1478729') ) + sin( radians('39.8657112') ) * sin( radians( `lat` ) ) ) ) < 0.100000

You see, I moved the whole calculation into the WHERE clause, and test that against your limiter.

Upvotes: 3

Related Questions