显示MYSQL数据库中多个表的数据

Question

I know there's something wrong here. Can anyone point it out? I am trying to display question_body from questions and answers_body from answers.

EDIT : I got this error: Column 'question_id' in field list is ambiguous

 <?php
        $auctionSurvey = "SELECT question_id, survey_id, question_body, answer_body FROM questions
                          INNER JOIN answers ON answers.question_id = questions.question_id
                          WHERE survey_id='1'";
        $aucResult = mysql_query($auctionSurvey) or die (mysql_error());

        while($auctionRow = mysql_fetch_assoc($aucResult)){
            echo $auctionRow['question_body']. $auctionRow['answer_body'];
        }
?>

Answer 1

It looks like one problem is ambiguous column...

In the SELECT list, I don't think MySQL can figure out which table is referenced by question_id.

The normative pattern whenever two (or more) tables (row sources) are referenced is to QUALIFY every column reference...

SELECT q.question_id
     , q.survey_id
     , q.question_body
     , a.answer_body
  FROM questions q
  JOIN answers a 
    ON a.question_id = q.question_id
 WHERE q.survey_id='1'

There's two big benefits to this pattern (qualifying column references):

Firstly, it helps future readers "know" which columns are from which table, without having to look at the table definitions and figure out which table has which column name.

Secondly, it helps avoid "breaking" a SQL statement when a new column with the same name as a column in another table is added... having the column references qualified avoids the potential for breaking a SQL statement (causing a working SQL statement to start raising an ambiguous column exception.