Advanced Query 1

[ice78991]

I am trying to work out a query which satisfies the following demand. I have tried various solutions with joins,temporary tables etc but none are working. Here's the problem:

The site has a forum. There are 2 tables. A Member table and a Posts Table.

The Member table contains member information.

The Posts table contains the member posts with a MemberID foreign key. These posts can be activa or inactive and there is a column to signify this.There is one row for each post.

What I want to do is generate a list of members along with the number of active or inactive posts they have made.

The ultimate result for me would be to return a table that contains a row for each member with activeposts and inactiveposts fields appended. Is this possible and if so what is the best apporach.

 

[gmmastros]

Take a look here: thread183-1159740



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hwkranger]

Assuming

Member table
ID LONG, IDENTITY
NAME VARCHAR(20)

Posts Table
ID Long, IDENTITY
HEAD VARCHAR(50)
POST VARCHAR(2000)
MEMBERID LONG
IsActive BIT

FK - MEMBERID - MEmberTable.ID

Select
  ID, 
  Name,
  Active = (Select Count(*) From Posts Where MemberID = M.ID and IsActive = -1),
  Unactive = (Select Count(*) From Posts Where MemberID = M.ID And IsActive = 0)
From
 Member M

Seems like it would work to me. It's not so terribly optimized....

I'm failing to see what the "ultimate results" means.... I'm not understanding what you want by the last paragraph.





Randall Vollen
Merrill Lynch

 

[eichen]

Ice, if you just want the counts, no verbiage, here's your solution:

-- this version returns only members having BOTH active AND inactive posts listed:

select M.*,count(PA.*) as ActiveCount, COUNT(PI.*) AS InactiveCount
from Members M
inner join posts PA on M.memberid=PA.memberid and PA.status = 'ACTIVE'
inner join posts PI on M.memberid=PI.memberid and PI.status = 'INACTIVE'


-- use left outer joins below to get ALL members, even those with 0 posts
select M.*,ISNULL(count(PA.*),0) as ActiveCount, ISNULL(COUNT(PI.*),0) AS InactiveCount
from Members M
left outer join posts PA on M.memberid=PA.memberid and PA.status = 'ACTIVE'
left outer join posts PI on M.memberid=PI.memberid and PI.status = 'INACTIVE'