MySQL showing duplicate rows

Question

I have two tables, people and comment.

Table: people
+-------------------+----------------+-------------+------+
| id |      cn      |       en       |     dob     | role |
+-------------------+----------------+-------------+------+
| 1  |  ChineseName |   EnglishName  |  1989-03-02 |   0  |
+-------------------+----------------+-------------+------+
| 2  | ChineseName2 |   EnglishName2 |  1923-06-12 |   1  |
+-------------------+----------------+-------------+------+

Table: comment
+----+--------+----------+-------------------+---------------------+
| id |  owner | owner_id | creator_person_id |       comment       |
+----+--------+----------+-------------------+---------------------+
| 1  | PERSON |     2    |          1        |  Some comments here |
+----+--------+----------+-------------------+---------------------+
| 2  | TRANSAC|     1    |          1        |       Comments      |
+----+--------+----------+-------------------+---------------------+
| 3  | PERSON |     1    |          1        |     Comments here   |
+----+--------+----------+-------------------+---------------------+

When I execute the query:

SELECT comments.comment, 
creator_person.id AS creator_id,
creator_person.cn AS creator_cn,
creator_person.en AS creator_en,
creator_person.dob AS creator_dob,
creator_person.role AS creator_role
FROM people, comments
JOIN people AS creator_person
ON comments.creator_person_id = creator_person.id AND comments.owner = 'PERSON' AND comments.owner_id = 1
ORDER BY people.id

I suppose it will return me only 1 row, however I'm getting a duplicate of that row:

+------------------+------------+-------------+-------------+-------------+--------------+
|      comment     | creator_id | creator_cn  | creator_en  | creator_dob | creator_role |
+------------------+------------+-------------+-------------+-------------+--------------+
|   Comments here  |      1     | ChineseName | EnglishName |  1989-03-02 |       0      |
+------------------+------------+-------------+-------------+-------------+--------------+
|   Comments here  |      1     | ChineseName | EnglishName |  1989-03-02 |       0      |
+------------------+------------+-------------+-------------+-------------+--------------+

Habibillah · Accepted Answer · 2013-11-28 01:30:25Z

1

you joining people table twice. So you can modify your query to become:

SELECT comments.comment, 
creator_person.id AS creator_id,
creator_person.cn AS creator_cn,
creator_person.en AS creator_en,
creator_person.dob AS creator_dob,
creator_person.role AS creator_role
FROM comments
INNER JOIN people AS creator_person
ON comments.creator_person_id = creator_person.id 
   AND comments.owner = 'PERSON' AND comments.owner_id = 1
ORDER BY creator_person.id

also add distinct keyword if same problem occur like:

SELECT distinct comments.comment, ...
...
...

edited Nov 28, 2013 at 1:30

answered Nov 28, 2013 at 1:19

Habibillah

28.8k5 gold badges40 silver badges59 bronze badges

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

2 Comments

user1494994 Over a year ago

thank you for the quick reply, however it gave me error on #1054 - Unknown column 'people.id' in 'order clause' How should i fix this?

Habibillah Over a year ago

than change it to ORDER BY creator_person.id. Also I have modify the answer

Kara · Accepted Answer · 2013-11-28 01:29:07Z

1

You should change:

FROM people, comments

to:

FROM comments

Since you are joining on the people table you do not need to include it in the FROM clause.

You will also need to update your order clause to reflect the alias name you gave the people table:

ORDER BY creator_person.id

edited Nov 28, 2013 at 1:29

answered Nov 28, 2013 at 1:18

Kara

6,23616 gold badges54 silver badges58 bronze badges

1 Comment

user1494994 Over a year ago

thank you for the quick reply, however it gave me error on #1054 - Unknown column 'people.id' in 'order clause' How should i fix this?

Filipe Silva · Accepted Answer · 2013-11-28 01:23:05Z

You have an extra join in there (an implicit one in people,comments since you are after doing an inner join with people you should remove the first).

select comments.comment,
  creator_person.id as creator_id,
  creator_person.cn as creator_cn,
  creator_person.en as creator_en,
  creator_person.dob as creator_dob,
  creator_person.role as creator_role
from comments
inner join people as creator_person
  on comments.creator_person_id = creator_person.id
where comments.owner = 'PERSON' and comments.owner_id = 1
order by creator_person.id;

sqlfiddle demo

I also moved the other validations from the on clause to the where clause. In this case, you are doing a inner join, it wouldn't make a difference, but this is a bad habit and could make you do weird things when you use left/right joins in the future

Collectives™ on Stack Overflow

MySQL showing duplicate rows

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related