On Thu, Jan 28, 2010 at 12:51 AM, Hans Hilzinger <ha...@cknet.co.za> wrote: > > > 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. >
Test each sub-query. If it works and returns results as expected, add it to another query. Keep doing it till you either get the result you want, or you reach a stage where the query stops returning results. Then you will know what is wrong. It is very inefficient to try and debug something like the above in one go. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, Wisconsin, United States _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
