SQL Server subquery for count in 2nd table

Question

I would like to add the count result of a 2nd query to the result set which queries the 1st table and connect this subquery with the ID of the 1st Table result. This simplified code explains it:

Select 
    ID_Field_Table1, 
    (Select Count(x) 
     From Table2 
     Where ID_Field_Table2 = ID_Field_Table1) As mycount 
From Table1

What is the correct syntax? Thanks

This code is simplified but the construction is the same. In my DB the ID_Field_Table2 and ID_Field_Table1 have the same name (let's say ID_Field). If I use this same Name, the SubQuery goes like "...Where ID_Field = ID_Field.." and this always Returns a Reslut, because it is not connecting the queries ("Select a Where x = x" returns any record in the table 2).

Select 
    ID_Field AS ID1, 
    (Select Count(x) 
     From Table2 
     Where ID_Field = ID1) As mycount 
From Table1

It just returns an error, saying that ID1 does not exist.

1. When I use the original name of the columns (which are the same), the result is wrong

2. When I us an alias, an error occurs

So I think the query is wrong at all.

Answer 1

Well, you can't use an alias defined in the select clause, but you can use the aliases defined in the from clause:

Select 
    ID_Field AS ID1, 
    (Select Count(x) 
     From Table2 
     Where ID_Field = t1.ID_Field) As mycount 
From Table1 t1

You can also do it without an alias, since the sub query is for a different table:

Select 
    ID_Field AS ID1, 
    (Select Count(x) 
     From Table2 
     Where ID_Field = Table1.ID_Field) As mycount 
From Table1

However, I suspect a query using left join to a derived table as suggested in esta's answer would have better performance.