Results 1 to 3 of 3

Thread: Complex Count

  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: Complex Count

    Hello All,

    I have this:
    "SELECT *, COUNT(replies.record_ID) " +
    "AS number_replies " +
    "FROM reports,replies " +
    "WHERE replies.record_ID=reports.record_ID " +
    "GROUP BY reports.record_ID";

    The problem with this is if the replies table has zero records, I don't get my record from the reports table. What would work is a subselect from within COUNT. Something like this.
    SELECT *, COUNT(

    SELECT * FROM replies
    WHERE replies.record_ID=reports.recordID)

    FROM REPORTS
    WHERE ......

    This, of course, is illegal. My situation is that I have a one to many relationship between two tables. The 'many' table has a foreign key to the unique primary key in the 'one' table. I want to select from the 'one' table, then count the number of entries in 'many' that match the primary key.

    Thanks in advance, Ray
    Last edited by raytucson; 10-17-04 at 22:09.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what would work even better is a LEFT OUTER join
    Code:
    select reports.record_ID
         , reports.foo
         , reports.bar
         , count(replies.record_ID) as number_replies
      from reports
    left outer
      join replies 
        on reports.record_ID 
         = replies.record_ID
    group 
        by reports.record_ID
         , reports.foo
         , reports.bar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    2
    Thanks

Posting Permissions

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