How to Make This Query in SQL Server Compact Edition

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.

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.

How to connect SQL Server Compact Edition database to Crystal Report in C#

So I found my solution thanks to this helpful CodeProject sample

I will demonstrate an easier sample to make it easier to figure it out.

1. Create a Winform and add a button and a CrystalReportViewer control to it.

2. Add a DataSet (*.xsd file) to your project using add -> New Items in solution explorer. After that, add a DataTable to the DataSet.

3. Add columns to DataTable. It's better to name them the same as the columns you are going to display on your report. The number of columns depends on how many columns should be displayed in the Crystal report.

4. Add a Crystal Report into the project using add -> New Items and using the Report Wizard, choose ADO.NET DataSets of the Project data source as the data source of the Crystal Report and select the data table you just created in your DataSet, as the selected table of the Crystal Report.

5. Click finish and your columns will automatically be added in CrystalReport.

6. Go to the button click event and write these codes in it.

   private void btnGo_Click(object sender, EventArgs e)
   {
       CrReport2 objRpt = new CrReport2();
       string query = "Select Name,Number from tblInfo";  //Your sql query
       SqlCeConnection conn =
           new SqlCeConnection(
              @"Data Source=|DataDirectory|\myDB.sdf;Persist Security Info=False"); //Your connection
   
       SqlCeDataAdapter adepter = new SqlCeDataAdapter(query, conn);
       DsReport Ds = new DsReport(); //DsReport is my dataset
   
       adepter.Fill(Ds, "customer"); //customer is my datatable in dataset
   
       objRpt.SetDataSource(Ds);
       crystalReportViewer1.ReportSource = objRpt;
   }
   

7. Enjoy your report :)

Query two separate SQL Compact databases from SQL Server Management Studio

No, it is not possible to Query SQL Compact databases across files. You must load the relevant data in memory, and use LINQ to Objects if you want a Consolidated result, or replicate the SQL Compact database to a single SQL Server database.

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.

Replace carriage return in SQL Server Compact edition

SQL Compact only supports Unicode, maybe you can use NCHAR ?

---

Related Topics