CODEWITHSUNDEEP
mysqlsqlselectgroup-byhaving
nsilva
nsilva

Reputation: 5612

MySQL - Select from duplicates where

SELECT sample_number, date_sampled, date_completed, COUNT(*) c FROM tbl_sample GROUP BY sample_number HAVING c > 1

The above query is giving me all the sample numbers that are duplicated as shown below:-

'483662','0000-00-00 00:00:00','0000-00-00 00:00:00','2'
'493812','2011-10-14 00:00:00','2011-10-28 11:24:11','2'
'497558','1970-01-01 01:00:00','1970-01-01 01:00:00','2'

Looking at sample number 483662 for example:-

'483662','0000-00-00 00:00:00','0000-00-00 00:00:00'
'483662','2011-09-02 00:00:00','2011-10-04 09:39:31'

I need to expand on this query and select all the duplicate sample numbers where date_completed is either '0000-00-00 00:00:00' or '1970-01-01 01:00:00'

Any idea how I can do this?

Upvotes: 2

Views: 48

Answers (2)

Obaidul
Obaidul

Reputation: 57

SELECT sample_number, COUNT(1) AS c 
FROM tbl_sample
WHERE sample_number IS NOT NULL 
AND (date_completed='0000-00-00 00:00:00' OR date_completed='1970-01-01 01:00:00')
GROUP BY sample_number 
HAVING COUNT(1) > 1

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT DISTINCT A.sample_number 
FROM tbl_sample A 
INNER JOIN (SELECT sample_number, COUNT(*) c 
            FROM tbl_sample 
            GROUP BY sample_number HAVING c > 1
           ) AS B ON A.sample_number = B.sample_number 
WHERE A.date_completed IN ('0000-00-00 00:00:00', '1970-01-01 01:00:00')

Upvotes: 1

Related Questions