Subquery in SQL Server Compact Edition

Question

I'm trying to do this in a SQL Server CE database, but the database engine keeps reporting errors.

SELECT  C.guid, C.name, C.updated, 
     C.hddsize, C.hddavailable, C.hddfree, 
     C.ramsize, C.profiles, C.cpu, 
     (SELECT COUNT(D.id) AS numprogs 
      FROM ComputerData AS D 
      WHERE D.computer_id = C.id) AS numprograms 
FROM Computers AS C;

I've been told SQL Server CE supports subqueries. Is there something I'm doing wrong?

Answer 1

My only experiences in queries are with MySQL, but hopefully it is similar enough.

Your query looks strange to me because your subquery is in the SELECT clause. I have never seen that before... but apparently it is supported in MySQL. Usually the subquery comes in the after a FROM or LEFT JOIN or JOIN.

Your example is simple enough that you could implement it with a LEFT JOIN:

SELECT C.guid, ..., COUNT(distinct D.id) as numprogs
FROM Computers AS C
LEFT JOIN ComputerData as D ON D.computer_id = C.id

In this case, LEFT JOIN is the correct type of join to use because even if there is no matching record in the D table for a particular C record, your result set will still contain that C record and numprogs will just be zero, as you would expect.

If you really want to use a subquery, try this:

SELECT C.guid, ..., S.numprogs
FROM Computers AS C
LEFT JOIN
(SELECT computer_id, COUNT(*) as numprogs
 FROM ComputerData GROUP BY computer_id) AS S
ON C.id=S.computer_id

I suggest simplifying your query to get it to be the simplest possible query that should work, but doesn't work. Then tell us the specific error message that your database engine is returning.

Edit: I loooked in the MySQL chapter about subqueries and it seems like you should try removing the "as numprograms" clause after your subquery... maybe you don't get any choice about the naming of the column that comes out of the subquery after you've already composed the subquery.

Answer 2

The limitation in SQL CE is that it does not support subqueries that return a scalar value. Subqueries that return a set are parsed fine.

The subquery in the join in Grayson's answer returns a set, so it should work. Sometimes a scalar subquery cannot be avoided in a join condition. By using 'IN' instead of '=', the parser can be tricked.

See my answer to this question.