Very dificult (advanced) query

[Pedro Fonseca]

Greetings!

Can someone please help me to crack this problem? I have 4 tables:
Subject, Forum, Topic and Post. A Subject groups various Forums, a
Forum groups various Topics, and a Topic groups several Posts. The
references inside the tables are:

+-----------+ +-----------+ +-----------+ +-----------+
| Subject | | Forum | | Topic | | Post |
+-----------+ +-----------+ +-----------+ +-----------+
| idsubject | | idforum | | idtopic | | idpost |
| idsubject | | idforum | | idtopic |

The query I was doing returned all the Forums grouped by Subjects.
Having this resultset I would then iterate through it (in a PHP
script) and for each row (i.e. Forum) I would then perform 2 other
queries: one to get the number of Topics in that Forum and another to
get the number of Posts in that Topic. This is, obviously, not the
best way of doing it - if I had 10 topics, I would end up querying the
database 21 times in one single PHP script... :-( This were the
original queries:

SELECT Subject.idsubject, Forum.idforum
FROM Subject, Forum
WHERE Subject.idsubject=Forum.idsubject
ORDER BY Subject.idsubject, Forum.idforum

Then for each row of this resultset I do these:

SELECT COUNT(idtopic)
FROM Topic
WHERE idforum=(script fills current idforum here)

SELECT COUNT(idpost)
FROM Post, Topic
WHERE Topic.idtopic=Post.idtopic
AND Topic.idforum=(script fills current idforum here)
I'm now try to optimize all the querying stuff in my website and I
would like to do this specific section in only one single query. I
achieved to get as far as returning all the Forums grouped by Subjects
with the respective number of Topics for each (i.e. I managed to have
the 2 first queries in only one). This is the new query:

SELECT Subject.idsubject, Forum.idforum, count(idtopic)
FROM Subject, Forum LEFT JOIN Topic ON Topic.idforum=Forum.idforum
WHERE Subject.idsubject=Forum.idforum
GROUP BY Subject.idsubject, Forum.idforum

But I can't seem to group the 3 queries in only one though... I can't
find a way to also return the total number of Posts for each Topic. I
tried:

SELECT Subject.idsubject, Forum.idforum, count(Topic.idtopic),
count(Post.idpost)
FROM Subject, Post LEFT JOIN (Forum LEFT JOIN Topic ON
Topic.idforum=Forum.idforum) ON Topic.idtopic=Post.idtopic
WHERE Subject.idsubject=Forum.idsubject
GROUP BY Subject.idsubject, Forum.idforum

I tried this one and many other, but I'm really just shooting in the
dark here... Again, what I wanted was to return a resultset with all
the Forums and their respective Subjects, together with the total
number of Topics for each Forum and the total number of Posts for each
Topic.

Is this even doable? I think so... Anyone care to help please?
Best regards.
Pedro Fonseca, from Portugal

[Bill Karwin]

Pedro Fonseca wrote:

Again, what I wanted was to return a resultset with all
the Forums and their respective Subjects, together with the total
number of Topics for each Forum and the total number of Posts for each
Topic.

Is this even doable? I think so... Anyone care to help please?

Olá, Pedro!

In my estimation this is not doable in one query (except for a UNION of
two incongruous queries).

To get the count of posts per topic, you would need one row in the
result set for each distinct topic. But you are aggregating the topics,
so the result set has zero, one or many topics represented in each row.

I suggest that you consider it a victory to have reduced 21 queries to
2, and move on to your next task. :-)

Regards,
Bill K.

[Pedro Fonseca]

Bill Karwin <bi**@karwin.com> wrote in message news:<cg********@enews2.newsguy.com>...

Pedro Fonseca wrote:

Again, what I wanted was to return a resultset with all
the Forums and their respective Subjects, together with the total
number of Topics for each Forum and the total number of Posts for each
Topic.

Is this even doable? I think so... Anyone care to help please?

Olá, Pedro!

In my estimation this is not doable in one query (except for a UNION of
two incongruous queries).

To get the count of posts per topic, you would need one row in the
result set for each distinct topic. But you are aggregating the topics,
so the result set has zero, one or many topics represented in each row.

I suggest that you consider it a victory to have reduced 21 queries to
2, and move on to your next task. :-)

Regards,
Bill K.

OK, I think I'll take your advise.
Obrigado Bill! ;-)

[Pedro Fonseca]

> > [Pedro Fonseca]

Again, what I wanted was to return a resultset with all
the Forums and their respective Subjects, together with the total
number of Topics for each Forum and the total number of Posts for each
Topic.

[Bill Karwin]
To get the count of posts per topic, you would need one row in the
result set for each distinct topic. But you are aggregating the topics,
so the result set has zero, one or many topics represented in each row.

Actually (I'm sorry, I really couldn't give up on this just yet :-),
what if I added a slight change to what I originally intended? What if
I was trying to have a result set with all the Forums and their
respective Subjects, together with the total number of Topics for each
Forum and the total number of Posts in that same Forum (note that it's
the grand total number of Posts for each Forum, not for each Topic). I
get the feeling that this is perhaps doable, but I could still be
wrong... Any help?

[Bill Karwin]

Pedro Fonseca wrote:

Actually (I'm sorry, I really couldn't give up on this just yet :-),
what if I added a slight change to what I originally intended? What if
I was trying to have a result set with all the Forums and their
respective Subjects, together with the total number of Topics for each
Forum and the total number of Posts in that same Forum (note that it's
the grand total number of Posts for each Forum, not for each Topic). I
get the feeling that this is perhaps doable, but I could still be
wrong... Any help?

This sounds more likely. Now you are counting the posts using the same
grouping criteria by which you grouped the count of topic id's, so this
creates no logical conflict.

But it depends on the MySQL behavior for GROUP BY, which I confess I
don't understand very well. In theory, the following query might work
in your example:

SELECT S.idsubject, F.idforum, COUNT(DISTINCT T.idtopic),
COUNT(DISTINCT P.idpost)
FROM Subject S INNER JOIN Forum F ON S.idsubject = F.idforum
LEFT JOIN Topic T ON T.idforum = F.idforum
LEFT JOIN Post P ON P.idtopic = T.idtopic
GROUP BY S.idsubject, F.idforum

My hesitation is that I get the idea that MySQL allows greater
flexibility with regards to computing multiple aggregates than the SQL
standard and some other RDBMS's generally allow. But I'm not sure
exactly how flexible it is. :-)

Regards,
Bill K.

[Pedro Fonseca]

no****@pedrofonseca.com (Pedro Fonseca) wrote in message news:<15*************************@posting.google.c om>...

[Pedro Fonseca]
Again, what I wanted was to return a resultset with all
the Forums and their respective Subjects, together with the total
number of Topics for each Forum and the total number of Posts for each
Topic.

[Bill Karwin]
To get the count of posts per topic, you would need one row in the
result set for each distinct topic. But you are aggregating the topics,
so the result set has zero, one or many topics represented in each row.

[Pedro Fonseca]
Actually (I'm sorry, I really couldn't give up on this just yet :-),
what if I added a slight change to what I originally intended? What if
I was trying to have a result set with all the Forums and their
respective Subjects, together with the total number of Topics for each
Forum and the total number of Posts in that same Forum (note that it's
the grand total number of Posts for each Forum, not for each Topic). I
get the feeling that this is perhaps doable, but I could still be
wrong... Any help?

It was hard but I came up with a solution to this problem - but many
thanks to anyone that might answer instead. For the sake of keeping
things for the record, for future reference, the query that allows
this is:

SELECT Subject.idsubject, Forum.idforum, count(DISTINCT
Topic.idtopic), count(DISTINCT idpost)
FROM Subject, (Forum LEFT JOIN Topic ON Forum.idforum=Topic.idforum)
LEFT JOIN Post ON Topic.idtopic=Post.idtopic
WHERE Subject.idsubject=Forum.idforum
GROUP BY Subject.idsubject, Forum.idforum

Again, this returns a result set with a row for each Forum and their
respective Subject, together with the total number of Topics for each
Forum as well as the total number of Posts for each Forum (not for
each Topic as mentioned earlier). Should a Forum not have a Topic yet,
0 is returned as the count of the Topics, and should an existing Topic
not have any Posts yet, 0 is also returned as the count of the Posts.
This way, every single Forum is returned, even if they still don't
have any Topics or Posts.

I'm moving on to the next problem now, but do you think this query can
be further optimized?

Best regards!

[Pedro Fonseca]

Bill Karwin <bi**@karwin.com> wrote in message news:<cg********@enews3.newsguy.com>...

Pedro Fonseca wrote:

Actually (I'm sorry, I really couldn't give up on this just yet :-),
what if I added a slight change to what I originally intended? What if
I was trying to have a result set with all the Forums and their
respective Subjects, together with the total number of Topics for each
Forum and the total number of Posts in that same Forum (note that it's
the grand total number of Posts for each Forum, not for each Topic). I
get the feeling that this is perhaps doable, but I could still be
wrong... Any help?

This sounds more likely. Now you are counting the posts using the same
grouping criteria by which you grouped the count of topic id's, so this
creates no logical conflict.

But it depends on the MySQL behavior for GROUP BY, which I confess I
don't understand very well. In theory, the following query might work
in your example:

SELECT S.idsubject, F.idforum, COUNT(DISTINCT T.idtopic),
COUNT(DISTINCT P.idpost)
FROM Subject S INNER JOIN Forum F ON S.idsubject = F.idforum
LEFT JOIN Topic T ON T.idforum = F.idforum
LEFT JOIN Post P ON P.idtopic = T.idtopic
GROUP BY S.idsubject, F.idforum

My hesitation is that I get the idea that MySQL allows greater
flexibility with regards to computing multiple aggregates than the SQL
standard and some other RDBMS's generally allow. But I'm not sure
exactly how flexible it is. :-)

Regards,
Bill K.

Indeed, that's perfect. ;-) We came up with pretty much the same
solution (I'm posting to this newsgroup through Google, so it takes
several hours before my post appears. When I posted my solution yours
wasn't here, sorry...). I'm also not that much aware myself of the
quircks offered by MySQL to optimize this sort of query. Eventually
I'll be giving that a look, but if anyone can suggest something just
by looking at the query, please do it. Thanks for being there Bill!

Best regards!