Combine multiple queries on the same mysql table

Question

I am trying to connect every answer in this table with a heading to his question. I understand that nested queries are a bad idea. Is there any other way to do this?

postid | type | parent_postid | title | content ---------------------------------------------------- 1 | question | NULL | car wheels | how many 2 | answer | 1 | NUll | 4 wheels SELECT * FROM table WHERE type = 'answer' while($row = mysql_fetch_array($result)) { $parent_postid = row['parent_postid']; SELECT title FROM table WHERE postid = '$parent_postid' } 

Answer 1

You can make an independent connection:

 select questions.postid, questions.title, answers.postid, answers.title, from table as questions inner join table as answers on (questions.postid = answers.parent_postid); 

Answer 2

 select question.postid as questionID, question.title as questionTitle, question.content as questionContent, answer.content as answerContent from table question inner join table answer on( question.postid=answer.parent_postid ) order by question.postid 

Note that you must use the alias of the columns, since they will have the same name, and you cannot distinguish it by the name of the column.

You also want to use orderby so you can group all the answers together with the corresponding question. You can scroll and start processing a new question each time the questionID changes.

Answer 3

 SELECT * FROM table WHERE type = 'answer' $ids = array(); while($row = mysql_fetch_array($result)) { $ids[] = $row['parent_postid']; } $query = "SELECT title FROM table WHERE postid IN (".implode(',',$ids).")"; 

You want to run a check to make sure you have the identifiers in your array before you run this query or you get an error message.

You can also do:

 $query = "SELECT title FROM table WHERE postid IN (SELECT parent_postid FROM table WHERE type = 'answer')"; 

Answer 4

You can simply combine two copies of the table to link questions and answers.

 SELECT q.title, a.content FROM table q JOIN table a ON a.parent_postid = q.postid