SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Non-Member
    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
    However, I also want to query another table with a count BUT it will only apply the first query statement:

    Code:
    (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

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •