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.

Answers


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

Need Your Help

Android ListActivity suddenly broke, can't find android.R.id.list even though its there

java android android-layout android-listview listactivity

I realize that many of these questions floats around out there but I never found anyone with my exact problem.

WWW::Mechanize::Firefox open url error

javascript jquery ajax perl

Am using WWW::Mechanize::Firefox in perl to automate a process. I can login without hassle by supplying the required fields and posting to the URL.