show only greater than 0 on a count statement 1

[snowboardr]

I have this SQL :

Select DISTINCT th.tid, th.tname, th.tmain, th.tcolor, (SELECT COUNT(star.stid) FROM userstars as star WHERE star.stid=th.tid AND sonuid=1) as starcount FROM thread th WHERE th.tmain=1

I need to show only records that return a starcount of > 0 but when try to add star count to either the count or the where section of the SQL i get a column could not be found... how can i do this?

Regards,
Jason

Jason

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

 

[r937]

SELECT * FROM (
SELECT th.tid
     , th.tname
     , th.tmain
     , th.tcolor
     , (SELECT COUNT(star.stid) 
          FROM userstars as star 
         WHERE star.stid=th.tid AND sonuid=1) as starcount 
  FROM thread th 
 WHERE th.tmain=1
) AS t
WHERE t.starcount > 0

notice i removed your DISTINCT, which was unnecessary

r937.com | rudy.ca