CODEWITHSUNDEEP
mysqlsql
user1869566
user1869566

Reputation: 110

Duplicate MySQL rows

I would like to write a query to pick out rows with the same cottageid, from, and to values, so in this case it would pick out the rows with the id 991, and 992. The table name is datesBooked.

enter image description here

Upvotes: 0

Views: 75

Answers (2)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

SELECT * FROM datesBooked
GROUP BY cottageid, from, to
HAVING COUNT(cottageid) > 1

Upvotes: 2

Alex Siri
Alex Siri

Reputation: 2864

You can join the table with itself:

select d1.id,d2.id from datesBooked d1
   inner join datesBooked d2 on 
      d1.cottageid=d2.cottageid
      and d1.from = d2.from
      and d1.to = d2.to
      and d1.id<d2.id

That would give you the duplicates. I am only getting the ones that have lesser id than the following one (that way you will get the duplicates in the right column, and the originals in the first one)

Upvotes: 2

Related Questions