duplicate column name error in mysql query

Question

I get an error of dupicate column name if fromm my query and dont know how to solve my query is

SELECT distinct(courseid+senderid+recipientid) as diskey,
       mess.id,
       User.firstName as recipientName,
       Course.name,
       senderid,
       recipientid,
       courseid,
       message,
       mess.status,
       mess.createdOn 
FROM 
(
    SELECT * 
    from Message,User,Course 
    where Message.recipientid=User.id 
        and Message.courseid=Course.id 
    order by Message.createdOn DESC
) as mess,
     User,
     Course 
WHERE senderid ='3' 
      OR recipientid='3' 
GROUP BY diskey;

can any one heplp

The error message usually includes the exact position of the error. So please attach the very message you get, while executing this query. — Sirko
– Sirko, Commented Nov 12, 2012 at 11:51
My suggestion would be to review SQL JOIN syntax - here is a great visual explanation of joins — Taryn
– Taryn, Commented Nov 12, 2012 at 11:52

souslov · Accepted Answer · 2015-01-15 11:35:50Z

7

To avoid duplication prefix the column name with its table as follows:

tab_name.col_name

edited Jan 15, 2015 at 11:35

souslov

44.7k11 gold badges92 silver badges113 bronze badges

answered Nov 12, 2012 at 11:49

SaidbakR

13.6k23 gold badges113 silver badges202 bronze badges

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

2 Comments

John Dvorak Over a year ago

+1 Without more details from the asker, anything above this would be guessing

am123 Over a year ago

my table structure is like Message | CREATE TABLE Message ( id int(11) NOT NULL AUTO_INCREMENT, courseid int(11) NOT NULL, senderid int(11) NOT NULL, recipientid int(11) DEFAULT NULL, message text NOT NULL, createdOn timestamp NULL DEFAULT CURRENT_TIMESTAMP, status tinyint(4) DEFAULT '0', fileName text, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |

SajjadHashmi · Accepted Answer · 2012-11-12 11:55:09Z

0

Try writing all column names attach with their respective table names:

Column names without table name attached in your query: … senderid,recipientid,courseid,message …

You should also write above as you are writing these in your query: … mess.id,User.firstName …

Hope this will solve the error.

answered Nov 12, 2012 at 11:55

SajjadHashmi

3,6952 gold badges21 silver badges22 bronze badges

1 Comment

am123 Over a year ago

coloumn name attach with table names doesnt work it still shows same error

Ijas Ameenudeen · Accepted Answer · 2013-01-21 09:02:27Z

0

Try replacing

WHERE senderid ='3' 
  OR recipientid='3'

with

HAVING senderid ='3' 
  OR recipientid='3'

Happy Coding !

answered Jan 21, 2013 at 9:02

Ijas Ameenudeen

9,2794 gold badges44 silver badges55 bronze badges

Comments

Rashedul.Rubel · Accepted Answer · 2013-07-12 10:37:23Z

0

to avoid duplication write query as below using prefix(tableName).columnname:

select id, name1,name2 from(
select table1.id,table1.name as name1, table2.name as name2 from
table1
innerjoin table2 on table1.id=table2.table1_id
)group by id

answered Jul 12, 2013 at 10:37

Rashedul.Rubel

3,61928 silver badges39 bronze badges

Collectives™ on Stack Overflow

duplicate column name error in mysql query

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related