CODEWITHSUNDEEP
phpmysql
Marcus
Marcus

Reputation: 4480

combine multiple queries on same mysql table

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'
}

Upvotes: 4

Views: 1673

Answers (4)

Kendrick
Kendrick

Reputation: 3787

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.

Upvotes: 1

Don Kirkby
Don Kirkby

Reputation: 56590

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

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

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);

Upvotes: 8

Geoffrey Wagner
Geoffrey Wagner

Reputation: 818

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')";

Upvotes: 0

Related Questions