how to get all the duplicates based on a field

[jasonc]

I am wanting to get all results where there is a duplicate 'Pid'

 

Pid is the Product ID.

 

 

INSERT INTO `products` (`Pid`, `productTitle`) VALUES

(3350, 'Product A'),

(3351, 'Product B'),

(3352, 'Product C'),

(3353, 'Product D'),

(3354, 'Product E'),

(3354, 'Product F'),

(3355, 'Product G'),

(3355, 'Product H'),

(3356, 'Product I'),

(3356, 'Product J');

 

from this table the results should be.

 

 

(3354, 'Product E')

(3354, 'Product F')

(3355, 'Product G')

(3355, 'Product H')

(3356, 'Product I')

(3356, 'Product J')

 

 

what formular should i use for this to work.

[jasonc]

this is the table layout.

 

CREATE TABLE IF NOT EXISTS `products` (

  `Pid` int(11) NOT NULL,

  `productTitle` text NOT NULL,

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[jasonc]

I have tried the following but this only returns one of the duplicates not all of them.

 

SELECT * FROM `products` GROUP BY `Pid` HAVING count(*) > 1

[fenway]

Well, you don't mean *, you mean Pid.

[DavidAM]

To get a list of the duplicate Pid values, you would use:

SELECT Pid FROM products GROUP BY Pid HAVING COUNT(Pid) > 1

 

Since you want the results to include other details from the row, you have to use that query in a WHERE clause:

 

SELECT Pid, productTitle
FROM products
WHERE Pid IN (SELECT Pid FROM products GROUP BY Pid HAVING COUNT(Pid) > 1)

[jasonc]

Hey thanks very well explained.  I see where I was going wrong now.

 

Thanks for your help on this.

 

 

To get a list of the duplicate Pid values, you would use:

SELECT Pid FROM products GROUP BY Pid HAVING COUNT(Pid) > 1

 

Since you want the results to include other details from the row, you have to use that query in a WHERE clause:

 

SELECT Pid, productTitle
FROM products
WHERE Pid IN (SELECT Pid FROM products GROUP BY Pid HAVING COUNT(Pid) > 1)