Monday, June 11, 2012

Nested queries with SQL Server Compact Edition

Following the previous post, I am finding some small troubles with SQL Server CE and their solutions, and I think they will be useful for anybody working with it.

SQL Server CE does not support nested queries!

The way to circumvent this is by using inner joins, as mentioned in this blog.

My query now reads:
String sql = "SELECT Authors.* FROM Authors INNER JOIN " +
                    "(SELECT AuthorID FROM SubDomain_Authors " +
                    "WHERE (DomainID = " + subdomain.DomainID +
                    ") AND (SubDomainID = " + subdomain.SubDomainID +
                    ")) AS t ON Authors.ID = t.AuthorID";

SqlCeCommand cmd = new SqlCeCommand(sql, con);

                SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (rs.HasRows)

No comments:

Post a Comment