Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007

    Unanswered: Query syntax: select parent record, including number of children?

    OK, I'm totally blanking on how to get my query to do the right thing. I have two tables, "SurveyQuestion" and "SurveyAnswer" with a one-to-many relationship: a Question has many Answers, and they're hooked up by including a field in Answer that specifies the Question ID.

    What I want to select is: all the columns of SurveyQuestion, and a count of how many SurveyAnswer records are associated with the SurveyQuestion. What's the right way to do this?? Should I make a stored proc or something to run the row count? Is there an easier way? I think the complication stems from the fact that I want to return a GROUP column alongside regular records, but I'm not really sure. I'm using MySQL 4.x if that affects your answer.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    4.x is not specific enough, you must be on at least 4.1 to run this --
         , Q.qux
         , ( select count(*)
               from SurveyAnswer 
              where questionID = Q.ID ) as answers
      from SurveyQuestion as Q | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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