Where is the sentence in the subquery expression in select

Question

Say I have a query like this:

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

I want the filter to be non-null for the "records" column.

I can not do it:

  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 result set to the table value parameter and get a separate query on this result set. Any ideas?

Answer 1

Just move it to the derived query. You cannot use the 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

Here you have to make small changes:

First of all, add the TOP clause to the subquery to force the query to return only one record in this table2. A subquery like this should only return a scalar value.

Secondly, a subquery can contain only one column in the list of columns, so the return value must be scalar.

Finally, you cannot filter the subquery or any made column in the select clause. Therefore, I recommend either using "join" or "exists" .

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

Or that:

 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 ) 

Greetings