CODEWITHSUNDEEP
mysql
Andrew Ehrlich
Andrew Ehrlich

Reputation: 287

In a MySQL SELECT query, can I use my "AS column" in a WHERE specification?

In my database I calculate distance between nodes with:

SELECT DISTINCT
       dest.name,
       AsText(orig.location),
       AsText(dest.location),
       round((glength(linestringfromwkb(linestring((orig.location), (dest.location)))))) as distance
FROM nodes orig,
     nodes dest
WHERE orig.name='GERP'

I would like to add 

and distance < dest.visibility

at the end, but am told that distance is an unknown column.

Any insight very much appreciated.

Upvotes: 0

Views: 274

Answers (5)

DVK
DVK

Reputation: 129403

You can not use the result set calculated column name in the where clause.

You need to (sadly) re-use the whole expression you used to calculate it

and round((glength(linestringfromwkb(linestring((orig.location), (dest.location))))))
    < dest.visibility

There are two work-arounds:

  1. If you create the text of the query outside of SQL (e.g. from Perl, PHP etc...), you can store the expression in a string before the query and re-use the string:

    $long_expr = "round((glength(linestringfromwkb(linestring((orig.location), (dest.location))))))";
$sql = qq[SELECT DISTINCT

    dest.name, AsText(orig.location), AsText(dest.location), $long_expr as distance ... and $long_expr < dest.visibility

  2. Store the original query (sans the last AND) in a temp table (but inlcude visibility column in the select), and then do the following

    SELECT * from #temp_table
WHERE distance < visibility

  3. Same as #2, but instead of temp table, do a subquery.

Upvotes: 0

Chris Shaffer
Chris Shaffer

Reputation: 32575

Use a subquery, then you only have to type the formula in once (and if you need to change it, you only need to change it once).

SELECT
       dest.name,
       dest.visibility,
       AsText(orig.location),
       AsText(dest.location),
       distance
FROM (
SELECT DISTINCT
       dest.name,
       dest.visibility,
       AsText(orig.location),
       AsText(dest.location),
       round((glength(linestringfromwkb(linestring((orig.location), (dest.location)))))) as distance
FROM nodes orig,
     nodes dest
WHERE orig.name='GERP'
) AS nodeDistances
WHERE nodeDistainces.distance < visibility

Upvotes: 2

chx
chx

Reputation: 11760

HAVING is your friend.

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.

To accommodate both standard SQL and the MySQL-specific behavior of being able to refer columns in the SELECT list, MySQL 5.0.2 and up permit HAVING to refer to columns in the SELECT list, columns in the GROUP BY clause, columns in outer subqueries, and to aggregate functions.

Note that HAVING can't be optimized and will be dog slow. Not a new problem with your query, though...

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332581

Use:

SELECT DISTINCT
       dest.name,
       AsText(orig.location),
       AsText(dest.location),
       round((glength(linestringfromwkb(linestring((orig.location), (dest.location)))))) as distance
  FROM nodes orig,
       nodes dest
 WHERE orig.name = 'GERP'
   AND round((glength(linestringfromwkb(linestring((orig.location), (dest.location)))))) < dest.visibility

SQL doesn't allow accessing column aliases in the WHERE clause -- the earliest MySQL supports is the GROUP BY clause.

Also, your query lacks criteria to join the two table instances -- resulting in a cartesian product.

Upvotes: 0

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

No, but you can add

and dest.visibility > round((glength(linestringfromwkb(linestring((orig.location), (dest.location))))))

mysql should be smart enough to only compute the expression once.

Upvotes: 1

Related Questions