CODEWITHSUNDEEP
mysqlsql
user3717821
user3717821

Reputation: 83

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

Upvotes: 0

Views: 103

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Use cross join:

select t1.col1, t1.col2, t2.col3 as data
from table1 t1 cross join
     (select col3 from table2) t2
where t2.col3 is not null;

Upvotes: 0

Related Questions