combine multiple queries on same mysql table

Question

I'm trying to connect each answer in this table with the title to its question. I understand that nested queries are a bad idea. Is there another 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'
}

cwallenpoole · Accepted Answer · 2011-06-10 19:10:40Z

8

You can do a self join:

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

edited Jun 10, 2011 at 19:10

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

answered Jun 10, 2011 at 19:09

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kendrick · Accepted Answer · 2011-06-10 19:12:31Z

1

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 you have to alias the columns since they'll otherwise have the same name and you wouldn't be able to differentiate by column name.

You also want to use the orderby so you can group all the answers together with the relevant question. You can loop through and start processing a new question everytime the questionID changes.

answered Jun 10, 2011 at 19:12

Kendrick

3,7871 gold badge25 silver badges42 bronze badges

Comments

Geoffrey Wagner · Accepted Answer · 2011-06-10 19:10:11Z

0

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 ids in your array before you run this query or you will get an error.

You could also do a:

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

answered Jun 10, 2011 at 19:10

Geoffrey Wagner

8181 gold badge5 silver badges11 bronze badges

Comments

Don Kirkby · Accepted Answer · 2011-06-10 19:11:29Z

0

You should be able to just join two copies of the table together to link the questions and answers.

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

answered Jun 10, 2011 at 19:11

Don Kirkby

56.7k28 gold badges225 silver badges307 bronze badges

Collectives™ on Stack Overflow

combine multiple queries on same mysql table

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related