user3717821 user3717821 - 1 month ago 9
MySQL Question

mysql storing subquery in column and performing where condition

i am performing a sub query in mysql which is like

select col1, col2 , (select col3 from table2) as 'data'
from table1
where not data is null


how should i get data in where clause. IS it POSSIBLE

Answer

One way to do this is :

SELECT *
FROM (
       select col1, col2 , (select col3 from table2) as 'data'
       from table1 
)t
WHERE data IS NOT NULL

As you see there I have created on derived table t for your query, now result of your query is treated as Table(temp table) and having columns as col1,col2 and col3, Using this result set we can able to access col3 in where clause .

Note - assuming that select col3 from table2 returns single value as per OP's comments