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?
Subquery rulesThe ntext, text, and image data types can't be used in the select list of subqueries. Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL ) can't include GROUP BY and HAVING clauses.
Subqueries are not allowed in the defining query of a CREATE PROJECTION statement. Subqueries are supported within UPDATE statements with the following exceptions: You cannot use SET column = {expression} to specify a subquery.
The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With