Where clause on subquery statement in select

Question

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?

Answer 1

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;

Answer 2

You should do some little modifications there:

First of all, add TOP clause on the subquery to force the query to return only one record of that table2. A subquery like this one you have should return only an scalar value.

Secondly, the subquery can have only one column in its column list, so again the return value should be a scalar one.

At last, you cannot filter the subquery or any made column in a select clause. So my recommendation is to either use "join"s or "exists".

Select col1, 
       col2
       from table1
       left outer join
            table2
       on table1.key = table2.key
       where not table2.key is null

Or this:

Select col1, 
       col2
       from table1
       inner join
            table2
       on table1.key = table2.key

Or this one:

Select col1, 
       col2
       from table1
       where exists (
            select *
                  from table2
                  where table2.key = table1.key
                  and   not table2.somethingelse is null
                  -- or any other relevant conditions
       )

Cheers