none
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
    Thursday, February 26, 2009 1:34 PM

Answers

  • 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
    • Marked as answer by phartlen Friday, March 06, 2009 10:13 AM
    Friday, March 06, 2009 9:46 AM

All replies

  • 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

    Friday, February 27, 2009 10:00 AM
  • 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


    Tuesday, March 03, 2009 2:29 PM
  • 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
    Wednesday, March 04, 2009 12:14 PM
  • 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
    Thursday, March 05, 2009 9:41 AM
  • 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
    • Marked as answer by phartlen Friday, March 06, 2009 10:13 AM
    Friday, March 06, 2009 9:46 AM
  • 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???
    Thursday, December 24, 2009 6:05 PM
  • 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
    Monday, December 28, 2009 4:00 PM