Query to select duplicates in column 2 based on column 1 in MySQL

Question

Let's say I have two columns: id and date.

I want to give it an id and it'll find all the duplicates of the value date of the column id.

Example:

id |date
1  |2013-09-16
2  |2013-09-16
3  |2013-09-23
4  |2013-09-23

I want to give it id 1 (without giving anything about date) and it'll give me a table of 2 columns listing the duplicates of id 1's date

Thanks in advance!

juergen d · Accepted Answer · 2013-09-16 14:44:00Z

3

select * from your_table
where `date` in
(
  select `date`
  from your_table
  where id = 1
)

or if you like to use a join

select t.* 
from your_table t
inner join
(
  select `date`
  from your_table
  where id = 1
) x on x.date = t.date

answered Sep 16, 2013 at 14:44

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

2 Comments

Thanks a bunch! :D I was wandering around documents about JOINs but I guess I looked at the wrong place

Actually, the IN clause is easier to understand. But thanks, again.