One of my mysql tables has duplicate rows and I'm cautious about just deleting them due to their auto increment ID's. I'm needing a fix for the below SQL query so that search result repetitions are limited to only being shown once ~ four hours of Googling and I still can't fathom what to do .

PHP Code:

$productQuery="SELECT C.*,I.* FROM `colour` AS `C` Left Join `product_inventory` AS `I` ON `C`.`productId` = `I`.`productId` where C.Colour_Id=".$_GET['colour'].$whrStr.$orderSort; 


Thats not the whole query, just the relevant part.

To find the duplicates I used:

PHP Code:

SELECT productId, Colour_Id, COUNT(*) FROM colour
GROUP BY productId, Colour_Id HAVING COUNT(*) > 1 


From what I've read I need to use SELECT DISTINCT though I can't work out how it applies to my query.

Anyone?

don't worry about auto increment id numbers, the database holds the last ID used separately to the data. So even deleting the last record added won't cause a problem.

If you don't need them, delete them.

SELECT DISTINCT fieldlist works across ALL the fields, so every column in the row will have to be identical for the record to be excluded.

Use the GROUP BY query to compress the duplicate records