solution to the problem as I understood it. The query listed here runs
properly on SQL Server 2000 and runs against the Northwind database that
Microsoft ships with that product. In this case, we are looking at
suppliers (instead of sections), products (instead of threads), and orders
(instead of posts). The subquery in the second join is what allows you to
get everything in a single recordset. Try it and let us know if it works.
SELECT s.companyName, s.city, s.region, s.country,
COUNT(p.productid) AS productCount,
sum(o2.ordercount) AS ordertotal
FROM suppliers s INNER JOIN products p
ON s.supplierid = p.supplierid
INNER JOIN (SELECT p.productid, COUNT(o.orderid) AS ordercount FROM
products p INNER JOIN [order details] o ON p.productid = o.productid GROUP
BY p.productid) AS o2
ON o2.productid = p.productid
GROUP BY s.companyName, s.city, s.region, s.country
_____________________
Eric
_____________________
"You can tell the ideals of a nation by its advertisements."
-- Norman Douglas
"Trevor Smith" <[EMAIL PROTECTED]>
08/20/2004 03:06 PM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: Inner joining with count and group by
Hello,
Thanks in advance for your time. I have a simple problem here, but cannot
seem to get my head around the right way to get the job done. I'm creating
a forum. There are three tables which we need concern ourselves with:
sections, threads and posts. Sections have threads in them, and users
create posts, which are inside of threads. What I want to create is the
main page, which lists all of the sections in the database, along with how
many threads AND posts each section currently has.
Here's the table layouts:
sections: secID (primary key), secTitle, secDescription
threads: thrID (primary key), thrTitle, thrSectionID (relating to secID)
posts: posID (primary key), posThreadID (relating to thrID), posTitle,
posDate, posAuthorID, posText
Here's the sql I've tried (which doesn't work, its only returns one
record):
SELECT secID, secTitle, secDescription, COUNT(t.thrID) AS secTotalThreads,
COUNT(p.posID) AS secTotalPosts
FROM sections AS s, threads AS t, posts AS p
WHERE t.thrSectionID = s.secID AND p.posThreadID = t.thrID
GROUP BY secID, secTitle, secDescription
This query returns multiple sections AND their correct number of threads,
but I cannot figure out how to add in the part for the posts to make it
work:
SELECT s.secID, s.secTitle, s.secDescription, COUNT(thrID) AS
secTotalThreads
FROM sections AS s, threads AS t
WHERE s.secID = t.thrSectionID
GROUP BY secID, secTitle, secDescription
Thanks again for your help,
Trevor Smith
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
