Jump to content

QUERY over 3 Tables


cschlaefcke

Recommended Posts

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

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

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;

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

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

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?

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

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.

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

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!!!

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...