Help with adding a count(*) to my query>

[7724]

Hi All,

I have a query whereby I query two tables as shown below, which works a treat:

( 
SELECT story_id AS id, published AS sdate, site_id, section_id, pic, headline, story, 'news' AS tbl
FROM stories
WHERE site_id =51
AND section_id = '2'
AND unix_timestamp( published ) <= unix_timestamp( NOW( ) )
 )
UNION ALL
 ( 
SELECT match_id AS id, match_date AS sdate, site_id, section_id, pic, headline, story, 'matchday' AS tbl
FROM match_details
WHERE site_id =51
AND section_id = '2'
AND unix_timestamp( match_date ) <= unix_timestamp( NOW( ) )
 )
ORDER BY sdate DESC , id DESC 
LIMIT 4

However, I also want to query another table with a count BUT it will only apply the first query statement:

(SELECT count(*) from comments WHERE comments.story_id = stories.story_id) as number_of_comments

Even if it means forcing the second query to hold a value of '0' for number_of_comments with the end result?

Is this at all possible?

Thanks

[swampBoogie]

( 
select story_id AS id, 
       published AS sdate, 
       site_id, 
       section_id, 
       pic, 
       headline, 
       story, 
       'news' AS tbl,
       (select count(*) 
          from comments 
         where comments.story_id = stories.story_id) as number_of_comments
  from stories
 where site_id =51
   and section_id = 2
   and published <= current_timestamp
)
UNION ALL
( 
select match_id, 
       match_date, 
       site_id, 
       section_id, 
       pic, 
       headline, 
       story, 
       'matchday',
       0
  from match_details
 where site_id =51
   and section_id = 2
   and match_date <= current_timestamp
)
 order by sdate desc, 
          id desc
 limit 4