cschlaefcke

SOLVED:The "DISTINCT" keyword within the COUNT´s really did the trick!The statement: SELECT F.FORUM_ID, COUNT(distinct T.FORUM_FK) AS topics, COUNT(distinct P.POST_ID) AS postsFROM forum F, topic T, post PWHERE T.FORUM_FK (+) = F.FORUM_ID AND P.TOPIC_FK (+) = T.TOPIC_IDGROUP BY F.FORUM_ID is now working!Thanks for the hints!Regards,Chris

Dammit! The Query executed on a MySQL-DB works just perfect.Can somebody tell me how to make it Oracle 8.1.7 compatible (No JOIN´s)???Regards,Chris

Yes you´re right ;-) It was a typo, so your version is exactly what my expression should have been at the beginning.@aspnetguy:I think I push the schemas into a MySQL-DB to see what I get when running the statement with the JOIN´s. In my Oracle-DB the JOIN-Statement is definitely not available! Maybe the distinct within the COUNT-Statement is the trick!!!Thanks for all the replies!Regards,Chris

Thanks for your suggestion!In the Oracle-DB we use (it´s 8.1.7 I guess) the "JOIN" keyword is not available. The JOIN could be created with the "(+)" which in my case makes it a LEFT JOIN. So I think the lines containing "JOIN" are equivalent to the lines with "(+)" and so they are redundant.Hopefully there are any Oracle-Gurus around here?

Hi Folks,I need to solve the following task on a Oracle 8 DB:3 Tables: Forum, Topic, Post (Funny, ey?)A forum has topics. A topic has posts.Table FORUMFORUM_ID | FORUM_DESC-------------------------------1 | Forum 12 | Forum 2Table TOPICTOPIC_ID | TOPIC_NAME | FORUM_FK--------------------------------------------1 | Topic 1 | 12 | Topic 2 | 23 | Topic 3 | 14 | Topic 4 | 1Table POSTPOST_ID | POST_TEXT | TOPIC_FK-----------------------------------------1 | Posttext 1 | 12 | Posttext 2 | 13 | Posttext 3 | 24 | Posttext 4 | 25 | Posttext 5 | 36 | Posttext 6 | 4I need a query that returns in one line:forum_id, count(topic_id), count(post_id)So I excpect:FORUM_ID | TOPICS | POSTS-----------------------------------1 | 3 | 42 | 1 | 2My query: SELECT f.FORUM_ID, COUNT(t.forum_id) AS topics, COUNT(p.post_id) AS postsFROM forum f, topic t, post p WHERE t.forum_id (+) = f.forum_id AND p.topic_id (+) = t.topic_id GROUP BY f.FORUM_ID; When I run it it returns the same values for TOPICS and POSTS.I tried different combinations with my where- and group-by-clause - no success :-(Can somebody help me?Thanks & Regards,Christian