Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

sqlite3 gives an 'ambiguous column name' error with the query below. The error itself references what seems to be about as an unambiguous column name as one can imagine, identifying the database as 'sourcedb', the table as 'answers', and the field as 'markup'. Why is this query triggering the error?

sqlite> SELECT answers.*
        FROM   sourcedb.answers
               INNER JOIN sourcedb.questions_tests
                 ON sourcedb.questions_tests.testskey = '121212eczema'
               INNER JOIN sourcedb.answers_questions
                 ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey
               INNER JOIN sourcedb.answers
                 ON sourcedb.answers.answerskey = sourcedb.answers_questions.questionskey; 
...>    ...>    ...> Error: ambiguous column name: sourcedb.answers.markup

Some additional detail...

sqlite> pragma table_info(answers);
0|markup|TEXT|0||0
1|identifier|TEXT|0||0
2|text|TEXT|0||0
3|answerskey|INTEGER|0||1
4|answertype|TEXT|0||0
5|ReadPerms||0||0
6|UpdatePerms||0||0
7|DeletePerms||0||0
sqlite> pragma database_list;
0|main|
2|sourcedb|/root/.ttest/database/sqlite/ttest-simple.sqlite
share|improve this question

closed as off-topic by Martin Smith, Mark Storey-Smith, StanleyJohns, Paul White, Jon Seigel Jul 21 '13 at 14:42

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – StanleyJohns, Paul White, Jon Seigel
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

up vote 4 down vote accepted

You have FROM sourcedb.answers and INNER JOIN sourcedb.answers so you are introducing the same table twice without giving either an alias.

Some of the join conditions appear probably wrong as well.

 FROM   sourcedb.answers
               INNER JOIN sourcedb.questions_tests
                 ON sourcedb.questions_tests.testskey = '121212eczema'

doesn't reference sourcedb.answers anywhere in the ON.

INNER JOIN sourcedb.answers_questions
                 ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey

Doesn't reference sourcedb.answers_questions in the ON

I presume you actually need something like

SELECT answers.*
FROM   sourcedb.answers
       INNER JOIN sourcedb.answers_questions
         ON sourcedb.answers.answerskey = sourcedb.answers_questions.questionskey
       INNER JOIN sourcedb.questions_tests
         ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey
WHERE  sourcedb.questions_tests.testskey = '121212eczema' 
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.