Display data from multiple tables in MYSQL database

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'];
        }
?>

What's the problem? No data returned? An error message? Does the query work when you run it in the database? — andrewsi
– andrewsi, Commented Dec 18, 2013 at 19:09
@andrewsi I got this error: Column 'question_id' in field list is ambiguous. — user3103739
– user3103739, Commented Dec 18, 2013 at 19:12
That means that there's more than one column with that name in your SELECT fields. You just need to qualify it by telling the database which one you want to use: SELECT questions.question_id.... — andrewsi
– andrewsi, Commented Dec 18, 2013 at 19:13
you need to identify the table for question_id so questions.question_id — Sam
– Sam, Commented Dec 18, 2013 at 19:13

spencer7593 · Accepted Answer · 2013-12-18 19:13:51Z

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.

BWS · Accepted Answer · 2013-12-18 19:14:04Z

0

Your error indicates that 'question_id' is in both tables. Use an 'alias' to differentiate between the tables, like this:

SELECT Q.question_id, survey_id, question_body, answer_body 
FROM questions Q
  INNER JOIN answers A ON A.question_id = Q.question_id
WHERE survey_id='1'";

answered Dec 18, 2013 at 19:14

BWS

3,85620 silver badges26 bronze badges

Comments

Mike Szyndel · Accepted Answer · 2013-12-18 19:22:50Z

0

You have to differentiate between the two tables:

SELECT Q.question_id, 
       A.question_id, 
       Q.survey_id, 
       Q.question_body,
       A.answer_body 
FROM questions Q 
INNER JOIN answers A ON A.question_id = Q.question_id 
WHERE Q.survey_id='1'

edited Dec 18, 2013 at 19:22

Mike Szyndel

10.6k11 gold badges51 silver badges64 bronze badges

answered Dec 18, 2013 at 19:13

Devendra Lattu

2,8122 gold badges22 silver badges27 bronze badges

Collectives™ on Stack Overflow

Display data from multiple tables in MYSQL database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related