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

Reply via email to