SQL Where to filter on Nested query
I have a query that looks like this:
Insert Into tblX (a,b,c) Select x as a, y as b (select top 1 whatever from tblZ as z where z.aID = y.aID order by z.s desc) as c from tblY as y where y.foo = 'bar' AND c <> NULL
The problem is that last line. It tells me that c is an invalid column name. using y.c as well, to the same result. I need to not inset rows where that giant inner query is null, because tblX cannot accept nulls there. I feel like I should be able to filter on that column, but I can't quite get the syntax right.
You will probably need to double-nest this query.
Also, depends on your DBMS, but you should be checking C IS NOT NULL
You are using two tables without a join. If you tell us what you are trying to achieve, we can help better.
What ended up working is double nesting the query.
Insert Into tblX (a,b,c) Select a,b,c from (select x as a, y as b, (select top 1 whatever from tblZ as z where z.aID = y.aID order by z.s desc) as c from tblY as y where y.foo = 'bar') as foobar Where c IS NOT NULL