CODEWITHSUNDEEP
sqlsql-server
anas p a
anas p a

Reputation: 411

Subquery where condition in SQL Server

I have some questions about where condition parameters while using subqueries.

select 
    id, name, 
    (select count(*) from table2) count 
from 
    table1

Output is 

+----+-------------+-------+
| id | name        | count |
+----+-------------+-------+
|  1 | abc         |    13 |
|  2 | efg         |    15 |
+----+-------------+-------+

I would like to use this count is where condition like this :

select 
    id, name, 
    (select count(*) from table2) count 
from 
    table1 
where 
    count = 15

But I get this error:

ERROR 1054 (42S22): Unknown column 'count' in 'where clause'

I don't want to use an entire subquery in where clause. How can we solve this issue?

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This query:

select id,name, (select count(*) from table2) as count
from table1

does not return the results that you have specified. How do I know? The count is different in the two rows. If this were the query, the count would be the same.

For your given query, the obvious solution is to move the subquery to the from clause:

select t1.id, t1.name, t2.cnt
from table1 t1 cross join
     (select count(*) as cnt from table2 t2) t2
where t2.cnt = 15;

However, your query is probably a correlated subquery. In that case the best solution is to use apply (which can be used for the uncorrelated case as well):

select t1.id, t1.name, t2.cnt
from table1 t1 outer apply
     (select count(*) as cnt
      from table2 t2
     ) t2
where t2.cnt = 15;

Upvotes: 2

jarlh
jarlh

Reputation: 44805

Use a derived table:

select * from
(
    select id,name, (select count(*) from table2) count from table1
) dt
where count = 15

Upvotes: 4

Related Questions