cschlaefcke
-
Posts
6 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by cschlaefcke
-
-
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,ChrisI 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!!! -
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,ChrisHmm...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. -
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?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;
-
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
QUERY over 3 Tables
in SQL
Posted
SOLVED:The "DISTINCT" keyword within the COUNT´s really did the trick!The statement:
is now working!Thanks for the hints!Regards,Chris