Subquery count

[snowboardr]

I have this sql statement where its selecting a bunch of folders from the folder table (userfolders) and then its counting how many messages are in that folder in table (privatemessages) well I need to take this sql statement one step further and grab a column from the privatemessage folder also called pstatus, however you can't use more then 1 column in this subquery below...

thanks, Jason

SELECT 
f.fid, f.fuid, f.fname, 
( SELECT COUNT(ptoid) 
FROM privatemessage 
WHERE ptoid=2 
AND 
pfolder=f.fid ) 
as intMailCount 
FROM userfolders 
as f 
WHERE fuid=2 
ORDER by fname ASC

 

[r937]

select f.fid
     , f.fuid
     , f.fname
     , PMcounts.pstatus
     , PMcounts.intMailCount 
  from userfolders as f 
inner
  join ( select pfolder
              , pstatus
              , count(ptoid) 
                  as intMailCount
           from privatemessage 
          where ptoid = 2 
         group
             by pfolder
              , pstatus
        )  as PMcounts
   on PMcounts.pfolder = f.fid 
 where fuid = 2 
order 
    by fname asc

r937.com | rudy.ca