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

rjkent · #1 (**permalink**) 01-08-07, 17:51

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.

r937 · #2 (**permalink**) 01-08-07, 20:22

4.x is not specific enough, you must be on at least 4.1 to run this --

Code:

select Q.foo
     , Q.bar
     , Q.qux
     , ( select count(*)
           from SurveyAnswer 
          where questionID = Q.ID ) as answers
  from SurveyQuestion as Q

Internet Services

Web Development

Digital Marketing

Consumer Tech