AndreaNobili AndreaNobili -3 years ago 199
SQL Question

How can I correctly select a MySql function value on a table having a specific alias?

I am not so into database and I have the following problem extracting geographical coordinates from a point object in a query. I am using MySql.

So I have a table like this:

Field Type Null Key Default Extra
------------------------------------------------------------------------------------
id bigint(20) NO PRI auto_increment
localization_id bigint(20) NO MUL
market_name varchar(255) NO
market_cod varchar(255) YES
description text YES
gps point YES


As you can see the query contains the gps field having type point.

Running this query it works fine:

SELECT
MD.market_name as market_name,
MD.description as market_description,
X(gps)as longitude,
Y(gps)as latitude
FROM MarketDetails as MD


And I am correctly retrieving the longitude and latitude field returned respectively by the X(gps) and Y(gps) functions.

The problem is that the MarketDetails table have the MD alias (because then I will put in JOIN with other tables), so I tried to alias also the previous X(gps) and Y(gps) functions, in this way:

SELECT
MD.market_name as market_name,
MD.description as market_description,
MD.X(gps)as longitude,
MD.Y(gps)as latitude
FROM MarketDetails as MD


But executing this query I am obtaining the following error message:

#42000FUNCTION MD.X does not exist


So, what is wrong? What am I missing? How can I correctly referring to the X(gps) and Y(gps) of the table that have the MD alias?

Answer Source

The alias goes on the columns, not the function:

SELECT MD.market_name as market_name,
       MD.description as market_description,
       X(MD.gps)as longitude,   
       Y(MD.gps)as latitude
FROM MarketDetails MD;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download