Nested SELECT clause in SQL Compact 3.5

Question

In this post "select with nested select" I read that SQL Compact 3.5 (SP1) support nested SELECT clause. But my request not work:

t1 - table 1 t2 - table 2 c1, c2 = columns

select 
 t1.c1, 
 t1.c2, 
 (select count(t2.c1) from t2 where t2.id = t1.id) as count_t 
from 
 t1

Does SQL Compact 3.5 SP1 support nested SELECT clause in this case?

Update:

SQL Compact 3.5 SP1 work with this type of nested request:

• SELECT ... from ... where .. IN (SELECT ...)
• SELECT ... from (SELECT ...)

Answer 1

Do you need a FROM clause in your nested select?

Answer 2

Maybe you need

select * from LogMagazines where id = (select max id from UserRoles)

?

Answer 3

Try running select max(Id) from UserRoles And make sure this returns a proper result. Then try select * from LogMagazines where id = With that result, its possible you have an error somewhere.

Answer 4

You're attempting to equate a scalar value with what is notionally a result set.

Try

select * from LogMagazines where id IN (select max(id) from UserRoles)

Ok, I answered the question and you asked a completely new and different question which is not quite how its supposed to work, but to answer the new question what you need to do is a join:

SELECT 
    t1.c1,  
    t1.c2,  
    count_t.c
FROM 
    t1 JOIN (select id, count(t2.c1) as c from t2 GROUP BY t2.id) count_t 
       ON t1.id = count_t.id

Or thereabouts

Answer 5

Thank all for help and advise.

The final answer for question - NO. SQL Compact 3.5 SP1 does not support nested select clause.