select with nested select

Question

Hi!

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?

Thanks,

 Peter

Answer 1

Hi Peter

SQL Compact doesnt support subqueries in Select clause.

A rewrite for your query that can work on SQL Compact would be

select t1.c1,t1.c2,CASE

WHEN (t3.cnt>0) THEN t3.cnt

ELSE 0 END

from t1 left outer join (select t2.C1,count(t2.C1) as cnt from t2 group by C1 ) as t3 on t1.C1 = t3.C1

 

Regards

Ramya

Answer 2

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...

Any suggestions?

Thanks,

 Peter

Answer 3

Hi,

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.

Thanks
Vipul

Answer 4

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?

Thanks,

 Peter

Answer 5

Oh, 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

Answer 6

Hello!

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???

Answer 7

Hi Sasha,

The nested select statement is not supported in SQL Server Compact.

Try the alternate query suggested by Ramya in the reply.

Regards,
Vipul