Selecting a parent and children in a single query

[therealwesfoster]

Here's what I'm needing to do.

 

I have 2 tables: "questions" and "answers". Each question can have a variable amount of answers to choose from, so they are in separate tables ("answers" connects to "questions" via answer_questionID).

 

I'm wanting to list each question with their answers below. Below is the current code:

$sql = mysql_query("SELECT * FROM questions");
while ($row = mysql_fetch_array($sql)){
    // Get the answers
    $sql2 = mysql_query("SELECT * FROM answers WHERE answer_questionID='{$row['question_id']}'");
    while ($row2 = mysql_fetch_array($sql2)){
        // Show the items
    }

    // Show the questions
}

 

Can I use a JOIN or SUBSELECT to put this into a single query? If not, any tips for optimization?

 

Thanks!

[fenway]

select q.*, a.* from questions as q left join answers as a on (a.answer_questionID = q.question_id)

[JustLikeIcarus]

I basically do this exact thing returning all answers combined in one result using GROUP_CONCAT().  Basically I do

select questions.question, GROUP_CONCAT(answers.answer) 
FROM questions, answers 
WHERE answers.answer_questionID = questions.question_id
GROUP BY questions.question

Obviously you would need to replace questions.question and answers.answer with your column name.

 

 

[fenway]

The problem with that is two-fold -- first, if you list is long, it can get truncated without your knowledge; second, if you ever want other fields back, you're screwed.

[JustLikeIcarus]

@fenway very true. It isn't the optimal solution. The best way is most likely what the OP already has.

[fenway]

@fenway very true. It isn't the optimal solution. The best way is most likely what the OP already has.

I disagree -- why not a simple join?

[JustLikeIcarus]

Well it depends... If he wants each question followed by its answers, Im thinking its best to loop through the questions fetching each ones answers and printing them. Or possibly pull back all questions into an array like $arr['question'][1] = ... and $arr['answers'][1][] ...  If your going to do everything in one query Im thinking you would want to pull back question_id, question, answer then print out a question only once then answers untill you hit a different question id... What are your thoughts?

[therealwesfoster]

select q.*, a.* from questions as q left join answers as a on (a.answer_questionID = q.question_id)

 

Thanks! I have tried a join and then print_r() the results, but it looks like it's only grabbing the question row and 1 answer row. I need it to return 1 questions row and (possibly) 5 answer rows all in the same array. Is there something I'm missing?

 

Thanks again for all of the responses

[fenway]

It returns one (q,a) pair per row -- that's different.

[JustLikeIcarus]

Sounds like to do one question row followed by its answers you would need to union 2 queries one that returns questions and one that returns answers.. now heres something i was just thinking could work

 

select * from (

select question_id, question from questions

union all

select question_id, answer from answers

)

order by question_id

 

that should output each question followed by its answers... i think.... maybe....

[fenway]

All you need to do is order the query I provided, and then loop through the resultset in application code.