Alex Rivera | Logout

Where clause on subquery statement in select

Asked 2012-11-06T23:29:31.257
16

Let's say I have a query like this:

   Select col1, 
          col2, 
          (select count(smthng) from table2) as 'records'
   from table1

I want to filter it to be not null for 'records' column.

I cannot do this: 

         Select col1, 
                col2, 
               (select count(smthng) from table2) as 'records'
          from table1
        where records is not null

The best I came up with is to write this resultset to a Table Value parameter and have a separate query on that resultset. Any ideas?

sql sql-server-2008-r2
Edit
Report

1 Answer

22

Just move it to a derived query. You cannot use a column defined in the SELECT clause in the WHERE clause.

Select col1, col2, records
from
(
    Select col1, 
           col2, 
           (select ..... from table2) as records
    from table1
) X
where records is not null;
answered 2012-11-06T23:45:22.020

Your Answer