How can I make this query in SQL Server Compact Edition?

Question

Hi, this subquery works in SQL Server:

select systemUsers.name, 
    (select count(id) 
     from userIncidences 
     where idUser = systemUsers.id ) 
from systemUsers

How can It be made in SQL Compact?

Thanks!

Answer 1

Try this:

SELECT su.Name, COUNT(ui.ID)
FROM systemUsers su
LEFT JOIN userIncidences ui ON ui.idUser = su.ID
GROUP BY su.Name

[Edit:]
I originally had an INNER JOIN just like Tomalak, but I realized that this would exclude users with no incidents, rather than show them with a 0 count. That might even be what you want, but it doesn't match your original.

Answer 2

There are cases when you can't avoid a subquery, for instance if you have to include calculated columns that use data from the current and the previous row. Consider this query, for instance:

SELECT     
     (Current.Mileage - Last.Mileage)/Quantity as MPG
FROM         
     GasPurchases AS Current
     LEFT OUTER JOIN GasPurchases AS Last
     ON Last.Date =
        (SELECT MAX(PurchaseDate)
        FROM GasPurchases
        WHERE PurchaseDate < Current.PurchaseDate)

It will cause a parsing error:

SQL Execution Error.

Error Source: SQL Server Compact ADO.NET Data Provider Error Message: There was an error parsing the query.

I found this thread on MSDN that has a workaround. By changing the subquery so that it returns a set instead of a scalar value, I was able to save and run the following query.

SELECT     
     (Current.Mileage - Last.Mileage)/Quantity as MPG
FROM         
     GasPurchases AS Current
     LEFT OUTER JOIN GasPurchases AS Last
     ON Last.Date IN
        (SELECT MAX(PurchaseDate)
        FROM GasPurchases
        WHERE PurchaseDate < Current.PurchaseDate)

Answer 3

Thanks guys, DoctaJonez, I found your little post the most helpful with my subquery. Your syntax seems to work with SQL Server Compact v3.5. Here is the query I tried (which works).

By the way, the hardcoded value you see (38046), I know at the time of running the query

insert into tLink (start,stop,associativeobject,linktype,id,name,guid,createTime,modifyTime,externalID,description,linkLabel,LinkDetails)
select newtable.id,stop,associativeobject,linktype,newtable.id,name,guid,createTime,modifyTime,externalID,description,linkLabel,LinkDetails from tLink l, (select id, '38046' as newid from tObject Where name = 'Step 1' and id <> '38046') as newtable
where l.start = newtable.newid and start in (38046)