Pedro Abreu Pedro Abreu - 28 days ago 8
MySQL Question

Nested MySQL queries column aliases

Is this a valid MySQL query, can you use aliases from columns outside the nested query? (series_id_a and series_id_b are function inputs, this is supposed to be used in a function)

select x1 as x1a, x2 as x2a, y1 as y1a, y2 as y2a FROM region WHERE series_id = series_id_a AND EXISTS(
SELECT x1 as x1b, x2 as x2b, y1 as y1b, y2 as y2b FROM region WHERE series_id = series_id_b AND x1b = x1a AND x2b = x2a AND y1a = y1b AND y2a = y2b
)

Answer

No, it's not valid to reference the column aliases assigned to the expressions in the outer query, in conditions in the WHERE clause.

For the sake of clarity, and the sanity of future readers, and of all that is good and beautiful in this world... qualify all column references.

As an example of what I am guessing you are trying to achieve:

 SELECT r.x1 AS x1a
      , r.x2 AS x2a
      , r.y1 AS y1a
      , r.y2 AS y2a 
   FROM region r
  WHERE r.series_id =  ?  -- series_id_a parameter
    AND EXISTS ( SELECT 1
                   FROM region q
                  WHERE q.series_id =  ? -- series_id_b parameter
                    AND q.x1 = r.x1
                    AND q.x2 = r.x2
                    AND q.y1 = r.y1
                    AND q.y2 = r.y2
                )
Comments