Subquery in SQL Server Compact Edition

Subquery in SQL Server Compact Edition

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.

SQL Server Compact won't allow subselect but inner join with groupby not allowed on text datatype

I think that the solution could be easier. I don't know exactly how is your metadata but I think that this code could fit your requirements by simply using LEFT JOIN.

    SELECT Nieuwsbrief.ID 
    , Nieuwsbrief.Titel 
    , Nieuwsbrief.Brief 
    , Nieuwsbrief.NieuwsbriefTypeCode 
    , COUNT(NieuwsbriefCommentaar.NieuwsbriefID) AS AantalCommentaren
FROM Nieuwsbrief  
LEFT JOIN NieuwsbriefCommentaar  ON (Nieuwsbrief.ID = NieuwsbriefCommentaar.NieuwsbriefID)
WHERE NieuwsbriefCommentaar.Goedgekeurd = 1

Edited: 2ndOption

SELECT N.ID, N.Titel, N.Brief, N.NieuwsbriefTypeCode, G.AantalCommentaren  FROM Nieuwsbrief as N LEFT JOIN (SELECT NieuwsbriefID, COUNT(*) AS AantalCommentaren FROM NieuwsbriefCommentaar GROUP BY NieuwsbriefID) AS G ON (N.ID = G.NieuwsbriefID) 

Please, let me know if this code works in order to find out another workaround..

regards,

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

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.

how to fix this query performance on SQL Server Compact Edition 4

I think you're running into a correlated subquery problem. The query part you're experimenting with is part of a JOIN condition, so it is fully evaluated for every potentially matching row. You're making your SQL engine do the second 'GROUP BY' for every row produced by the FROM clause. So it's reading 2192 rows to do the group by for each and every row produced by the FROM clause.

This suggest you're getting 73 rows in the FROM clause grouping (2192 * 73 = 160 016)

When you change it to do SELECT 1, you eliminate the table-scan read for grouping.

SQL Subqueries throwing an error

This should work for SQL Server:

SELECT Name, Position, Salary
FROM Employee E
   JOIN ( SELECT Max(Salary) as Sal FROM Employee ) M  ON E.Salary = M.Sal

Here is the SQL Fiddle.

--EDIT

It sounds like you are using SQL Server CE which does not fully support nested sub queries. Try something like this instead using the IN keyword:

SELECT e.Name, e.Position, e.Salary
FROM Employee e
WHERE e.Salary IN (SELECT MAX(Salary) as sal 
                   FROM Employee)

Nested SELECT clause in SQL Compact 3.5

Thank all for help and advise.

The final answer for question - NO. SQL Compact 3.5 SP1 does not support nested select clause.

---

Related Topics