COUNT (*) inside select 1

[snowboardr]

I am doing something wrong i would like to do a count inside this select for example:

SELECT th.tid, th.tname, th.tmain 
(SELECT COUNT(*) as postnumber FROM forumpost AS post WHERE post.ptid=th.tid)  
FROM thread as th WHERE tmain=1

is it possible to do two counts on one select? in different tables? I need to count the following in the same select statement:

SELECT COUNT(*) as postnumber FROM forumpost AS post WHERE post.ptid=th.tid

and

SELECT COUNT(*) as postnumber FROM forumpost AS post WHERE post.ptid=th.tid AND ptype=0

Jason

[red]Army[/red] : [white]Combat Engineer[/white] : [blue]21B[/blue]

 

[r937]

SELECT th.tid
     , th.tname
     , th.tmain 
     , c.postnumber
     , c.ptype0number
  FROM thread AS th 
INNER
  JOIN ( SELECT ptid
              , COUNT(*) as postnumber 
              , SUM(
                 CASE WHEN ptype = 0
                      THEN 1 
                      ELSE 0 END ) as ptype0number
           FROM forumpost 
         GROUP BY ptid ) AS c
    ON c.ptid = th.tid
 WHERE th.tmain = 1

r937.com | rudy.ca

 

[snowboardr]

thank you for your input and that works almost! the only problem is its only returning 2 records, the ones with actual posts in them, how do i get it to return the thread topics even if they don't have posts?

Regards,
Jason

Jason

[red]Army[/red] : [white]Combat Engineer[/white] : [blue]21B[/blue]

 

[r937]

make it a LEFT OUTER JOIN

r937.com | rudy.ca