How to list all duplicates based on selected id

Asked Jan 23 '20 at 09:54

Active Jan 23 '20 at 10:05

Viewed 28 times

I am trying to display duplicates from my tables based on an id but I got all entries including data that didn't have duplicates .

SELECT table1.document_type_id,table1.document_id,table1.document_file_name,table1.document_no as 'name',table1.document_name  as  'department_name',tbl_stacks.stack_name,table1.created_at,table1.document_created_by,table1.document_modified_by,table1.document_size 
FROM tbl_documents as table1 
LEFT JOIN tbl_stacks ON table1.stack_id = tbl_stacks.stack_id 
WHERE (table1.document_file_name IN (
    SELECT tbl_documents.document_file_name 
    FROM tbl_documents 
    GROUP BY document_file_name 
    HAVING count(*) > 1 
    ORDER BY table1.document_id ASC) 
AND table1.document_file_name != '') 
AND table1.document_type_id = 111 
ORDER BY table1.document_file_name ASC 
LIMIT 10 OFFSET 1

edited Jan 23 '20 at 10:05

asked Jan 23 '20 at 09:54

Lara

1

Please show example data or table – Shanteshwar Inde Jan 23 '20 at 09:57
There's no point in using `ORDER BY` in a query used in `WHERE xxx IN`. The order doesn't matter. – Barmar Jan 23 '20 at 09:58
I think this is answer of your question https://stackoverflow.com/a/854177/8544723 – Vinay Kaklotar Jan 23 '20 at 10:00
@VinayKaklotar Isn't his code in one of those answers? – Barmar Jan 23 '20 at 10:01
This looks like it should work. But you say you want to display duplicates based on ID. This is looking for duplicates based on the file name. – Barmar Jan 23 '20 at 10:02
i joined tables based on id and selected duplicates based on file name. – Lara Jan 23 '20 at 10:12
*display duplicates from my tables based on an id* There is NO a field `id` in both sample data and shown query. And what records in shown data do you consider as duplicates? PS. Specify MySQL version. – Akina Jan 23 '20 at 10:51

How to list all duplicates based on selected id

0 Answers0