Folks
I am considering porting one or more public web sites I have running sql express databases to sqllite. This has been prompted due to the inability of my service provider to sufficiently support this kind of hosting. I have done a number of tests including conversion of our aspnet membership and role providers from sql server to sqllite and all seems to work well. I am however not able to get more complex queries to work and was wondering if someone can tell me whether or not sqllite supports nested sub queries. I have included an example below. select b.BlogId, bp.BlogPostId, bp.Title, a.FirsName as FirstName, a.ScreenName, c.[Title] as Category, bp.Body, a.Email, s.[description] as [Status], b.CreatedDate, b.UpdatedDate , (select count(*) from BlogComment where BlogComment.BlogPostId = bp.BlogPostId and BlogComment.StatusId = 1) as New , (select count(*) from BlogComment where BlogComment.BlogPostId = bp.BlogPostId and BlogComment.StatusId = 2) as Approved , (select count(*) from BlogComment where BlogComment.BlogPostId = bp.BlogPostId and BlogComment.StatusId = 3) as Declined , (select count(*) from BlogComment where BlogComment.BlogPostId = bp.BlogPostId) as Total from Blog b inner join BlogPost bp on bp.BlogId = b.BlogId inner join BlogAuthor a on a.BlogAuthorId = b.AuthorId inner join BlogPostCategory bpc on bpc.PostId = bp.BlogPostId inner join BlogCategory c on c.BlogCategoryId = bpc.CategoryId inner join BlogStatus s on s.BlogStatusId = bp.StatusId where b.BlogId = @blogId order by b.CreatedDate desc The query seems to run but returns 0 values for the nested queries which means something is not working. If I parse the query using a open source tool like SQLIte Administrator it complains. The strange thing is when the query runs using the ADO.NET 2.0 provider no errors or warnings etc are returned. I would really appreciate your feedback. tks Hans Hilzinger _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
