|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
January 23rd, 2003, 10:22 PM
|
|||
|
|||
|
select query with count
this may be simple but i haven't yet been able to figure it out...i have three tables invloved in this query...one for forums, one for the threads, and one for posts...the format is like this...
forums forumID | forumTitle threads threadID | threadForum posts postID | postThread all the IDs are auto increasing integers. threadForum is a foreign key that links a certain thread to which forum it is part of and postThread is a foreign key that links it to a thread...a thread has a series of posts related to it. with the lowest postID being the first post in the thread obviously. now how do i return a recordset with the rows being each forums title, a count of how many threads there in the forum, and a count of how many posts there are in a forum? the main problem i'm getting is that i can't get an aggregate function like count() to go with a row for each forum...does this involve two subqueries somehow?? anyone got any tutorials that will help? rob
__________________
Delenda est Carthago
|
|
#2
January 24th, 2003, 02:57 AM
|
||||
|
||||
|
What is the query you are trying to do?
SELECT forum.title, count(threads.threadID), count (posts.postID) FROM (posts INNER JOIN threads ON posts.postThread = threads.threadID) INNER JOIN forum ON threads.threadForum = forum.forumID GROUP BY posts.postID, threads.threadID Could something like this help?
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins
|
|
#3
January 24th, 2003, 02:56 PM
|
|||
|
|||
|
Code:
select title,
(select count(*)
from threads
where threadforum = f.forumid )
as threads,
(select count(*)
from posts inner join threads
on posts.posthread = threads.threadid
where threads.threadforum = f.forumidid )
as posts
from forum f
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > select query with count |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
