Thursday, February 26, 2009 1:34 PMHi!
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?
Friday, February 27, 2009 10:00 AMHi Peter
SQL Compact doesnt support subqueries in Select clause.
A rewrite for your query that can work on SQL Compact would be
Tuesday, March 03, 2009 2:29 PMHi 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...
Wednesday, March 04, 2009 12:14 PMHi,
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.
Thursday, March 05, 2009 9:41 AMHmm.. 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?
Friday, March 06, 2009 9:46 AMOh, that explains it. We added support for it in version 3.5. You will have to upgrade to 3.5 or better 3.5 SP1.
I couldn't come up with any alternative query for this in 3.1
- Marked As Answer by phartlen Friday, March 06, 2009 10:13 AM
Thursday, December 24, 2009 6:05 PMHello!
I tried to use nested select request from post #1 and get the error:
"There was an error parsing the query. [ Token line number = X,Token line offset = XX,Token in error = select ]"
I use SQL Compact 3.5 SP1. Can you explain this???
Monday, December 28, 2009 4:00 PMHi Sasha,
The nested select statement is not supported in SQL Server Compact.
Try the alternate query suggested by Ramya in the reply.