MySQL error: duplicate column

Question

I'm having a bit of a problem with the following MySQL query and I can't find the source of it.

MySQL tells me that

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'annonce_dispo_id'

SELECT MAX(max_price) AS `max_price`,
       COUNT(*) AS `nb_annonces`,
       SUM(nb_dispo) AS `nb_dispo`
FROM
  (SELECT `annonce`.`id`,
          CEIL(MAX(price)*1.16) AS `max_price`,
          COUNT(DISTINCT annonce.id) AS `nb_annonces`,
          COUNT(annonce_dispoo.annonce_dispo_id) AS `nb_dispo`,
          `annonce_dispo1`.*,
          `annonce_dispo2`.*
   FROM `annonce`
   LEFT JOIN `annonce_dispo` AS `annonce_dispoo` ON (annonce_dispoo.annonceId = annonce.id
                                                     AND STR_TO_DATE(annonce_dispoo.dispo_date, '%d/%m/%Y') >= CURDATE())
   INNER JOIN `annonce_dispo` AS `annonce_dispo1` ON annonce.id = annonce_dispo1.annonceId
   INNER JOIN `annonce_dispo` AS `annonce_dispo2` ON annonce.id = annonce_dispo2.annonceId
   WHERE ((annonce.city IN
             (SELECT `cities`.`id`
              FROM `cities`
              WHERE (cities.label LIKE 'lyon%'))
           OR annonce.zipcode = 'lyon')
          OR (annonce.city LIKE '28674'
              OR annonce.zipcode = '28674'))
     AND (annonce_dispo1.dispo_date = '27/05/2014')
     AND (annonce_dispo1.disponibility = 'available')
     AND (annonce_dispo2.dispo_date = '31/05/2014')
     AND (annonce_dispo2.disponibility = 'available')
     AND (annonce.visible = 1)
     AND (annonce.completed = 1)
   GROUP BY `annonce`.`id` HAVING (nb_dispo >= 1)) AS `t`

I thought gave a different alias for the table in each JOIN I use them in, and can't really put my finger on what else is possible to output such an error.

sgeddes · Accepted Answer · 2014-05-25 20:06:26Z

2

Don't select annonce_dispo1.* and annonce_dispo2.* in your subquery, duplicated column names are being returned. Instead select the fields you need and alias accordingly.

SELECT MAX(max_price) AS `max_price`,
       COUNT(*) AS `nb_annonces`,
       SUM(nb_dispo) AS `nb_dispo`
FROM
  (SELECT `annonce`.`id`,
          CEIL(MAX(price)*1.16) AS `max_price`,
          COUNT(DISTINCT annonce.id) AS `nb_annonces`,
          COUNT(annonce_dispoo.annonce_dispo_id) AS `nb_dispo`,
          `annonce_dispo1`.field, `annonce_dispo1`.otherfield,
          `annonce_dispo1`.field as field2, `annonce_dispo1`.otherfield as otherfield2
   FROM `annonce`
   LEFT JOIN `annonce_dispo` AS `annonce_dispoo` ON (annonce_dispoo.annonceId = annonce.id
                                                     AND STR_TO_DATE(annonce_dispoo.dispo_date, '%d/%m/%Y') >= CURDATE())
   INNER JOIN `annonce_dispo` AS `annonce_dispo1` ON annonce.id = annonce_dispo1.annonceId
   INNER JOIN `annonce_dispo` AS `annonce_dispo2` ON annonce.id = annonce_dispo2.annonceId
   WHERE ((annonce.city IN
             (SELECT `cities`.`id`
              FROM `cities`
              WHERE (cities.label LIKE 'lyon%'))
           OR annonce.zipcode = 'lyon')
          OR (annonce.city LIKE '28674'
              OR annonce.zipcode = '28674'))
     AND (annonce_dispo1.dispo_date = '27/05/2014')
     AND (annonce_dispo1.disponibility = 'available')
     AND (annonce_dispo2.dispo_date = '31/05/2014')
     AND (annonce_dispo2.disponibility = 'available')
     AND (annonce.visible = 1)
     AND (annonce.completed = 1)
   GROUP BY `annonce`.`id` HAVING (nb_dispo >= 1)) AS `t`

See here for an example that doesn't work:

http://sqlfiddle.com/#!2/9bb13/1

answered May 25, 2014 at 20:06

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jay Zus Over a year ago

The fact that I attributed different aliases wouldn't mean that there aren't duplicate fields ? Like for one it would be annonce_dispo1_annonce_dispo_id and for the other one it would be annonce_dispo2.annonce_dispo_id, meaning that no duplication are supposed to happen (?) I thought it was also supposed to resolve any risk of ambiguity.

adrisons · Accepted Answer · 2014-05-25 21:13:15Z

0

The problem is that you are selecting all columns in the tables annonce_dispo1 and annonce_dispo2. The fact that you have attributed different table names doesn't mean that there aren't duplicate column names. I mean, you should use [Table name].[column name]
Example:

  (SELECT `annonce`.`id`,
      CEIL(MAX(price)*1.16) AS `max_price`,
      COUNT(DISTINCT annonce.id) AS `nb_annonces`,
      COUNT(annonce_dispoo.annonce_dispo_id) AS `nb_dispo`,
      `annonce_dispo1`.annonce_dispo_id AS `column1`,
      `annonce_dispo2`.annonce_dispo_id AS `column2`

I hope I've helped

answered May 25, 2014 at 21:13

adrisons

3,7335 gold badges35 silver badges52 bronze badges

Collectives™ on Stack Overflow

MySQL error: duplicate column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related