Reuse Subquery from Select Expression in WHERE-Clause

Question

Of course it isn't possible to write

SELECT (some subselect) AS blah FROM t WHERE blah = 'const'

What is the best way to do this?

score 6 · Accepted Answer · edited Apr 19 '11 at 18:56

6

you can move (some subselect) as table in the FROM :

SELECT s.blah
  FROM t, (some subselect) s
 WHERE t.id = s.id
   AND s.blah = 'const'

edited Apr 19 '11 at 18:56

AndreKR

answered Apr 19 '11 at 18:40

manji

but then you'd have to give up the `join on` syntax and use the dirty `where` join syntax? – Johan Apr 19 '11 at 19:27
Why is that? you can always use both (`s` is a table on its own). And why `where` is dirty? it's also a join (cross join) and can be useful. – manji Apr 19 '11 at 19:44

1 Answers1