Only display duplicate records in database

Question

I am trying to create a query to find any duplicates values and then just display those records. But my query is returning rows that don't contain any duplicates.

                SELECT j.*,             
                count(alldok.DocketNo)  AS ConsignmentCount,
                alldok.DocketNo  AS Dockets
                FROM job_new j                      
                LEFT JOIN job_dockets alldok ON alldok.JobID = j.ID
                WHERE j.IsActive = 1 
                GROUP BY j.ID  
                HAVING  ConsignmentCount > 1  
                ORDER BY  ID  DESC

A few of the rows that get returned:

We can see it found CAROL EMAIL twice but those other values are not duplicates but the count is saying 2, even though they only exist once.

Refer this : https://stackoverflow.com/a/854177/8150502 – Santosh Jun 13 '17 at 09:02 — Santosh, Jun 13 '17 at 09:02

score 2 · Answer 1 · answered Jun 13 '17 at 09:05

2

This will return you all your duplicate docketNo

SELECT DocketNo FROM job_dockets group by DocketNo having count(*) >= 2

answered Jun 13 '17 at 09:05

Santosh

393
2
11

But my query is coming from the `job_new` table then left joining to `job_dockets`. So how to do I count the `DocketNo` field? – user123456789 Jun 13 '17 at 10:09
can you create sample date so it will be more clear to query out - http://sqlfiddle.com/ – Santosh Jun 13 '17 at 10:25

Only display duplicate records in database

1 Answers1