Your query looks perfectly correct. You say that it returns 0 in count(*) sub-selects and you think that it's not correct? Could you show as your schema creation statements? And check that your StatusId really contains integers, not text. Also check that BlogPostId in BlogComment and BlogPost has the same data type.
Be careful: in SQLite type of value in the table is not 100% defined by the type you declared during table creation. SQLite allows to insert any type anywhere (doing some automatic conversions in some cases) and often it compares 1 (integer) and '1' (text) as different (also depends on where those values are stored and how are they compared). You can read more about that here: http://www.sqlite.org/datatype3.html. Pavel On Thu, Jan 28, 2010 at 1: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. > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
