CODEWITHSUNDEEP
phpmysqlduplicates
rsphorler
rsphorler

Reputation: 41

How to list records with duplicates indexes in database

I'm trying to write a single query that will pull from a table all records that are duplicate entries with different values. The purpose is to enable a user to mark which record in a set is the primary observation.

Table Fields: 

idTable (Index), idPlant, idParameter, Value (tinytext), IsPrimary (Yes/No, default No)

I want to be able to show only records that have identical idPlant and idParameter where the Value is different but I want to list the actual records not the number of replicates (which is easy to do). The user can then for each (idPlant and idParameter) group select which idTable is the primary data to update the table and set IsPrimary to 'Yes'.

I'm sure this must be possible but the only way I can see to do it so far is a multi-step process of both MySQL and PHP processing.

I have not been able to get the answers to the similar question to work I think because I am grouping on two columns.

I tried using the SQL fiddle but could not get that to work either. So I've included the create SQL statement and some example data

I am trying to create a Query that would display the entire record for idPhenotype = 3,4,5 (because one of those does not match) and 7,8 (again because one does not match) but ignores idPhenotype 10,11 because the PhenotypeValue is identical

Table SQL

CREATE TABLE IF NOT EXISTS `phenotype` 
 (  `idPhenotype` int(10) unsigned NOT NULL AUTO_INCREMENT
 ,  `idPhenotypeDef` int(11) DEFAULT NULL
 ,  `idPlant` mediumint(9) NOT NULL,  `PhenotypeParameter` tinytext
 ,  `PhenotypeValue` text
 ,  `PhenotypeDescribedBy` tinytext
 ,  `NewestData` enum('Yes','No') NOT NULL DEFAULT 'Yes'
 ,  PRIMARY KEY (`idPhenotype`)
 ,  UNIQUE KEY `idPhenotype_UNIQUE` (`idPhenotype`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=509165 ;

Data SQL

INSERT INTO `phenotype` (`idPhenotype`, `idPhenotypeDef`, `idPlant`
, `PhenotypeParameter`, `PhenotypeValue`, `PhenotypeDescribedBy`
, `NewestData`) VALUES
 (1, 1, 1, 'Front', 'Green', 'Historic Data', 'Yes')
,(2, 1, 2, 'Front', 'Green', 'Historic Data', 'Yes')
,(3, 1, 3, 'Front', 'Green', 'Historic Data', 'Yes')
,(4, 1, 3, 'Front', 'Dark Green', 'Historic Data', 'Yes')
,(5, 1, 3, 'Front', 'Green', 'Historic Data', 'Yes')
,(6, 1, 19, 'Front', 'Green', 'Historic Data', 'Yes')
,(7, 1, 20, 'Front', 'Green', 'Historic Data', 'Yes')
,(8, 1, 20, 'Front', 'Brown', 'Historic Data', 'Yes')
,(9, 1, 205, 'Front', 'Green', 'Historic Data', 'Yes')
,(10, 2, 1, 'Back', 'Green', 'Historic Data', 'Yes')
,(11, 2, 1, 'Back', 'Green', 'Historic Data', 'Yes')
,(12, 2, 12, 'Back', 'Green', 'Historic Data', 'Yes')
,(13, 2, 13, 'Back', 'Green', 'Historic Data', 'Yes')
,(14, 2, 14, 'Back', 'Green', 'Historic Data', 'Yes')
,(15, 2, 15, 'Back', 'Green', 'Historic Data', 'Yes');

http://sqlfiddle.com/#!9/346b4

Upvotes: 0

Views: 74

Answers (1)

Sergio Bernardo
Sergio Bernardo

Reputation: 368

I believe you need "HAVING" contruction:

Try this:

SELECT * FROM phenotype
WHERE CONCAT(idPlant,'-',PhenotypeParameter) IN (
    SELECT CONCAT(idPlant,'-',PhenotypeParameter) 
    FROM phenotype 
    GROUP BY idPlant, PhenotypeParameter 
    HAVING COUNT(*) > 1
)

Upvotes: 1

Related Questions