SitePoint Sponsor |
|
User Tag List
Results 1 to 2 of 2
-
Dec 17, 2008, 10:49 #1
- Join Date
- Feb 2005
- Posts
- 737
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Help with adding a count(*) to my query>
Hi All,
I have a query whereby I query two tables as shown below, which works a treat:
Code:( 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
Code:(SELECT count(*) from comments WHERE comments.story_id = stories.story_id) as number_of_comments
Is this at all possible?
Thanks
-
Dec 17, 2008, 12:06 #2
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
Code:( 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
Bookmarks