Use where condition on alias name of subquery

Question

SELECT
   ID
 , NAME
 , EMAIL
 , (SELECT Address FROM TABLE1 where T1ID=ID) as NewAddress 
FROM TABLE2 
WHERE NewAddress LIKE '%string%';

Here in this query i want to check condition on NewAddress Column, I don't want to pass where condition inside subquery(Having some reason)...

I mean to say i want to check condition on alias name of subquery here NewAddress

Here i am getting error that Unknown column 'NewAddress' in 'where clause'

You can't, you have to use a `HAVING` to compare field-list values. — Jared Farrish, Jul 04 '14 at 13:41
possible duplicate of [WHERE vs HAVING](http://stackoverflow.com/questions/2905292/where-vs-having) — Jared Farrish, Jul 04 '14 at 13:43

score 1 · Answer 1 · answered Jul 04 '14 at 13:43

1

My suggestion would be do away with the subquery and rewrite your query with a join:

SELECT  t2.ID,
        t2.Name, 
        t2.Email,
        t1.Address AS NewAddress 
FROM    Table2 AS t2
        INNER JOIN Table1 AS t1
            ON t1.ID = t2.T1ID
WHERE   t1.Address like '%string%';

It will solve your problem of filtering on that column and almost certainly perform better. A win-win scenario.

answered Jul 04 '14 at 13:43

GarethD

68,045
10
83
123

You could also put the condition on the `JOIN`. – Jared Farrish Jul 04 '14 at 13:48

score 0 · Answer 2 · answered Jul 04 '14 at 13:52

0

You could put parts of your query in another subquery like thie:

SELECT * FROM
(
 SELECT
 ID
 , NAME
 , EMAIL
 , (SELECT Address FROM TABLE1 where T1ID=ID) as NewAddress 
 FROM TABLE2
 ) AS SUBQUERY_01
WHERE NewAddress LIKE '%string%';

answered Jul 04 '14 at 13:52

schlonzo

1,409
13
16

Use where condition on alias name of subquery

2 Answers2