On 7/15/2011 9:54 AM, Gabriele Favrin wrote:
> SELECT
> otopic.id AS idtopic, otopic.titolo,
> strftime('%d/%m/%Y', otopic.inserito) AS inserito,
> (SELECT strftime('%d/%m/%Y %H:%M', max(inserito)) FROM topic WHERE pub=1
> AND id=otopic.id) AS ultimo,
I don't understand this part. Presumably, topic.id is unique, so there's
exactly one record where id=otopic.id. What's the point of max() ? How
can this subselect produce anything different from otopic.inserito? Did
you mean to select from messagi here?
> (SELECT count(id)||' '||max(id) FROM messaggi WHERE pub=1 AND
> topic=otopic.id) AS topicpostinfo,
> otopic.sticky, utenti.nick, utenti.altnick, utenti.pub AS upub
> FROM topic AS otopic
> INNER JOIN utenti ON otopic.idnick=utenti.id
> INNER JOIN categorie ON otopic.categoria=?
You join with categorie table, but you don't place any conditions on any
of its fields. Thus, you end up with a full cartesian product. I bet you
only have one category currently in the table, or you'd have noticed
that all the records were duplicated.
> WHERE otopic.pub=1
> ORDER BY otopic.sticky,otopic.inserito DESC
Try something like this:
SELECT otopic.id AS idtopic, otopic.titolo,
strftime('%d/%m/%Y', otopic.inserito) AS inserito,
count(*) as msgCount, max(m.id) as lastMessage,
otopic.sticky, utenti.nick, utenti.altnick, utenti.pub AS upub
FROM topic AS otopic
INNER JOIN utenti ON otopic.idnick=utenti.id
join messagi m on (m.topic=otopic.id and m.pub=1)
WHERE otopic.categoria=? and otopic.pub=1
group by otopic.id
ORDER BY otopic.sticky,otopic.inserito DESC;
You certainly want an index on messagi.topic
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
