mysqlmariadbmariasql

"Duplicate column name" error that shouldn't exist


I'm trying to migrate a database from sqlite to mariaDB ( which I never worked with ) and I can't seem to get past this problem!

When I try to run this, I always get

"#1060 - Duplicate column name 'id_document'"

Here's the query:

SELECT
  a.id_document AS id_document,
  id_user_associatedDoc,
  id_user_creator,
  stage,
  title,
  goldStandardAnnotationManual,
  count(content) AS answer_count
FROM
  (SELECT * 
  FROM Document join DocumentProject 
  ON Document.id_document = DocumentProject.id_document 
  WHERE DocumentProject.id_project = 2) a
  LEFT JOIN
  (SELECT * 
  FROM Annotation 
  WHERE Annotation.id_project = 2) b 
  ON a.id_document = b.id_document
GROUP BY a.id_document;

I don't understand why I should be getting this error! Can you help?


Solution

  • This first subquery syntax returns two id_document column:

    (SELECT *
     FROM Document
       join DocumentProject
       ON Document.id_document = DocumentProject.id_document
     WHERE DocumentProject.id_project = 2) a
    

    A quick way to fix this:

    SELECT
      a.id_doc /*Change this column as following*/ AS id_document,
      id_user_associatedDoc,
      id_user_creator,
      stage,
      title,
      goldStandardAnnotationManual,
      count(content) AS answer_count
    FROM
      (SELECT *, Document.id_document as "id_doc" /*define the first id_document column as a different name*/
      FROM Document
        join DocumentProject 
        ON Document.id_document = DocumentProject.id_document 
      WHERE DocumentProject.id_project = 2) a
      LEFT JOIN
      (SELECT * 
      FROM Annotation 
      WHERE Annotation.id_project = 2) b 
        ON a.id_document = b.id_document
    GROUP BY a.id_document;
    

    You can also pre-define which column you want to return inside the subquery as well. Example:

    (SELECT Document.id_document, Document.column_2, Document.column_3
     FROM Document
       join DocumentProject
       ON Document.id_document = DocumentProject.id_document 
     WHERE DocumentProject.id_project = 2) a
    

    That way, your outer query should select the a.id_document according to what you have pre-define inside the subquery and no need to change anything from the outer query.