Link to home
Get AccessLog in
Avatar of skij
skijFlag for Canada

asked on

MySQL: Get All Duplicates (not grouped)

Using MySQL, how can I get a list of ALL items that have a duplicate `NumericID`?

In this example, it only shows the first matching duplicate.

http://sqlfiddle.com/#!9/ccb53/13/0
CREATE TABLE `fruits` (
  `Name` varchar(255) NULL,
  `x` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `fruits` VALUES 
('Peach','269'),
('Pear','269e'),
('Mango','67'),
('Orange','954'),
('Raspberry','75'),
('Cherry','93'),
('Prune','198'),
('Grape','73'),
('Lemon','954'),
('Lime','954'),
('Strawberry','75'),
('Aprocot','48');

SELECT `Name`, TRIM(TRAILING 'e' FROM x) AS `NumericID`
FROM fruits 
GROUP BY NumericID
having count(*) >= 2

Open in new window

For example, the query above returns this:      
Peach
Raspberry
Orange

I want it to return:

Peach
Pear
Orange
Lemon
Lime
Strawberry
Raspberry
Orange
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

SELECT `Name`, TRIM(TRAILING 'e' FROM x) AS `NumericID`
FROM fruits 
WHERE NumericID IN
(SELECT TRIM(TRAILING 'e' FROM x) AS NumericID 
FROM fruits 
GROUP BY NumericID HAVING COUNT(*) >= 2)

Open in new window


(Edited)
ASKER CERTIFIED SOLUTION
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access