CODEWITHSUNDEEP
mysqlsqldatabasesql-functionmysql-function
AndreaNobili
AndreaNobili

Reputation: 42957

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?

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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;

Upvotes: 1

Related Questions