cschlaefcke Posted December 20, 2005 Share Posted December 20, 2005 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 Link to comment Share on other sites More sharing options...
Kcarson Posted December 20, 2005 Share Posted December 20, 2005 You need to join your tables based on the field you are joining on, try this out: SELECT f.FORUM_ID, COUNT(t.forum_id) AS topics, COUNT(p.post_id) AS postsFROM forum fJOIN topic t ON f.FORUM_ID = t.FORUM_FKJOIN post p ON t.TOPIC_FK = p.TOPIC_FKWHERE t.forum_id (+) = f.forum_id AND p.topic_id (+) = t.topic_id GROUP BY f.FORUM_ID; Link to comment Share on other sites More sharing options...
cschlaefcke Posted December 20, 2005 Author Share Posted December 20, 2005 You need to join your tables based on the field you are joining on, try this out:SELECT f.FORUM_ID, COUNT(t.forum_id) AS topics, COUNT(p.post_id) AS postsFROM forum fJOIN topic t ON f.FORUM_ID = t.FORUM_FKJOIN post p ON t.TOPIC_FK = p.TOPIC_FKWHERE t.forum_id (+) = f.forum_id AND p.topic_id (+) = t.topic_id GROUP BY f.FORUM_ID; <{POST_SNAPBACK}> 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? Link to comment Share on other sites More sharing options...
aspnetguy Posted December 20, 2005 Share Posted December 20, 2005 does an INNER JOIN make any difference from a LEFT JOIN? Might be worth a try. Link to comment Share on other sites More sharing options...
aspnetguy Posted December 20, 2005 Share Posted December 20, 2005 hey, here's the solution, I finally got it! SELECT f.FORUM_ID AS FORUM, count(distinct t.topic_id) AS TOPICS, count(distinct p.post_id) AS POSTSFROM forum fINNER JOIN topic t ON f.FORUM_ID = t.FORUM_FKINNER JOIN post p ON t.TOPIC_ID = p.TOPIC_FKGROUP BY f.FORUM_ID; Link to comment Share on other sites More sharing options...
Kcarson Posted December 20, 2005 Share Posted December 20, 2005 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?<{POST_SNAPBACK}> Hmm...didn't know that very interesting. I was wondering what the (+) was for. In that case, it looks like you may have just typoed the column names for your joins (see bold below):SELECT f.FORUM_ID, COUNT(t.forum_FK) AS topics, COUNT(p.post_id) AS postsFROM forum f, topic t, post pWHERE t.forum_FK (+) = f.forum_idAND p.topic_FK (+) = t.topic_idGROUP BY f.FORUM_ID;Hopefully, if aspnetguy's solution doesn't work, then maybe this will. Link to comment Share on other sites More sharing options...
cschlaefcke Posted December 21, 2005 Author Share Posted December 21, 2005 Hmm...didn't know that very interesting. I was wondering what the (+) was for. In that case, it looks like you may have just typoed the column names for your joins (see bold below):SELECT f.FORUM_ID, COUNT(t.forum_FK) AS topics, COUNT(p.post_id) AS postsFROM forum f, topic t, post pWHERE t.forum_FK (+) = f.forum_idAND p.topic_FK (+) = t.topic_idGROUP BY f.FORUM_ID;Hopefully, if aspnetguy's solution doesn't work, then maybe this will.<{POST_SNAPBACK}> 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 Link to comment Share on other sites More sharing options...
cschlaefcke Posted December 21, 2005 Author Share Posted December 21, 2005 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!!!<{POST_SNAPBACK}> 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 Link to comment Share on other sites More sharing options...
cschlaefcke Posted December 21, 2005 Author Share Posted December 21, 2005 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 Link to comment Share on other sites More sharing options...
Kcarson Posted December 21, 2005 Share Posted December 21, 2005 SOLVED:The "DISTINCT" keyword within the COUNT´s really did the trick!The statement...is now working!Thanks for the hints!Regards,Chris<{POST_SNAPBACK}> Glad to help, and even more glad that you were able to figure it out yourself and enlighten us as to how you did it. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now