I have some trouble getting what I want from my SQL Compact database. The idea is to get the entire contents of one table, and then for each record getting the count from another table corresponding to the main table row.
something like this:
t1 = table 1, main table
t2 = table 2, sub table
c1 - c2 = columns 1 - 2
select t1.c1, t1.c2, (select count(t2.c1) from t2 where t2.id = t1.id) as count_t from t1
This query works fine in full SQL 2005 version but not in compact, is there any way to solve this without using the illegal statement?
Hi and thanks for your reply.
I've tried your suggestion on both full SQL server and SQL Compact but it won't run on SQL CE. Here's my code:
1 select 2 rubrik.foretag, rubrik.fordelning, rubrik.linje, rubrik.station, rubrik.spanning, rubrik.lopnr, n2.cntstp as cnt 3 from rubrik join 4 ( 5 select 6 nyckel.rubnr, count(nyckel.lopnr) as cntstp 7 from 8 nyckel 9 group by 10 nyckel.rubnr 11 ) as n2 on rubrik.lopnr = n2.rubnr 12 order by rubrik.lopnr
As you can see I removed the case statement, which I don't think is neccessary, or? This works fine on Full SQL but not CE...
Could you be more specific when you say it works on full sql but not on CE, does that mean SqlCe throws any error or gives wrong results? If its later can you point out the difference with small repro.
I tried running a similar query and it seems to be working fine.
The CASE statement is necessary if you also want the rows for which count is 0.
Hmm.. it just says "There was an error parsing the query. [ Token line number = 5,Token line offset = 5,Token in error = select ]"
It seems it doesn't like the second select statement, that creates table n2...
I am using SQL CE 3.1, could this be something that is supported in 3.5 but not in 3.1? What version are you testing against?