CODEWITHSUNDEEP
phpmysqlsql
Amol
Amol

Reputation: 343

Use where condition on alias name of subquery

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'

Upvotes: 0

Views: 89

Answers (2)

schlonzo
schlonzo

Reputation: 1406

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%';

Upvotes: 0

GarethD
GarethD

Reputation: 69769

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.

Upvotes: 1

Related Questions