CODEWITHSUNDEEP
mysqlsqljoingroup-by
noot
noot

Reputation: 103

Find duplicate entries on date and title

I'm trying to identify duplicates in our database and I'd like to match on a combination of f.[summary] and f.[date].

Since the dates aren't unique, I'm wondering if this is possible. The way the query is written now, it will match only on f.[summary]

select
  f.[summary] as [Title],
  f2.[Possible_Duplicate_Count],
  f.[date] AS [Date],
  f.[type] as [Type], 
  f.[category] as [Category], 
  f.[status] as [Status], 
  (select u.[name] from [user] u where (u.[id] = f.[user])) as [User Name], 
  fp.[summary] as [Template]
from [form] f
left outer join [form] fp on (fp.[id] = f.[parent])
inner join
(
  select f.[summary], count(*) as [Possible_Duplicate_Count]
  from [form] f
  group by f.[summary]
  having count(*) > 1
) f2 on (f.[summary] = f2.[summary])

where
(
  (f.[parent] IN ('9e3c47d4-4f47-49bb-8ad4-4709c966ad38', '74069d7e-5ad6-4e7e-85f7-5402e67637ee'))
  and
  (f.[status] IN ('Confirmed'))
)

Upvotes: 1

Views: 53

Answers (1)

Joe Taras
Joe Taras

Reputation: 15379

Try this:

select
f.[summary] as [Title],
f2.[Possible_Duplicate_Count],
f.[date] AS [Date],
f.[type] as [Type], 
f.[category] as [Category], 
f.[status] as [Status], 
(select u.[name] from [user] u where (u.[id] = f.[user])) as [User Name], 
fp.[summary] as [Template]
from [form] f
left outer join [form] fp on (fp.[id] = f.[parent])
where exists(
    select 'duplicate'
    from [form] f2
    where f2.summary = f.summary
    and f2.date = f.date
    and f2.id != f.id)
and
(
  (f.[parent] IN ('9e3c47d4-4f47-49bb-8ad4-4709c966ad38', '74069d7e-5ad6-4e7e-85f7-5402e67637ee'))
and
  (f.[status] IN ('Confirmed'))
)

Upvotes: 1

Related Questions