Find duplicate rows in a table

debugcn Published at Dev

fr3d

I´ve got a list of partners, every partner item has the value partner_main=0. The person who enters the content for the partner items knows if a partner name partner_name is there twice or more and if so, he sets partner_main=1 on one of the rows.

If there is more than one partner with the same partner_name, the partner with the entry partner_main=1 should only show up next to the other partner rows that are not duplicate.

Mysql code:

SELECT *, COUNT(partner_name)
FROM partner
WHERE partner_name IN 
       (
         SELECT partner_name 
         FROM partner partner_main=1
       )
GROUP BY partner_name
HAVING (COUNT(partner_name) > 1)
UNION
SELECT *, COUNT(partner_name)
FROM partner
WHERE partner_name NOT IN 
       (
         SELECT partner_name 
         FROM partner partner_main=1
       )
GROUP BY partner_name
HAVING (COUNT(partner_name) > 1)

The error log says:

[26-Feb-2014 17:28:56 GMT] PHP Fatal error: Uncaught exception 'Exception' with message 'Query error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=1)
GROUP BY partner_name
HAVING ( COUNT(partner_name) > 1 )
UNION
SELECT *, COU' at line 3 (SELECT *, COUNT(partner_name)
FROM mm_partner
WHERE partner_name IN (SELECT partner_name FROM mm_partner partner_main=1)
GROUP BY partner_name
HAVING ( COUNT(partner_name) > 1 )
UNION
SELECT *, COUNT(partner_name)
FROM mm_partner
WHERE partner_name NOT IN (SELECT partner_name FROM mm_partner partner_main=1)
GROUP BY partner_name
HAVING ( COUNT(partner_name) > 1 ))' thrown in /wwwroot.wwwnew/system/modules/core/library/Contao/Database/Statement.php on line 282
#0 /wwwroot.wwwnew/system/modules/core/library/Contao/Database/Statement.php(253): Contao\Database\Statement->query()
#1 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Rules/SimpleQuery.php(75): Contao\Database\Statement->execute(Array)
#2 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Filter.php(102): MetaModels\Filter\Rules\SimpleQuery->getMatchingIds()
#3 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Setting/Collection.php(212): MetaModels\Filter\Filter->getMatchingIds()
#4 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/FrontendFilter.php(201): MetaModels\Filter\Setting\Collection->getParameterFilterWidgets(Array, Array, Object(MetaModels\FrontendIntegration\FrontendFilterOptions))
#5 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/FrontendFilter.php(76): MetaModels\FrontendIntegration\FrontendFilter->getFilters()
#6 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/Module/Filter.php(74): MetaModels\FrontendIntegration\FrontendFilter->getMetaModelFrontendFilter(Object(MetaModels\FrontendIntegration\Module\Filter))
#7 /wwwroot.wwwnew/system/modules/core/modules/Module.php(148): MetaModels\FrontendIntegration\Module\Filter->compile()
#8 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/Module/Filter.php(63): Contao\Module->generate()
#9 /wwwroot.wwwnew/system/modules/core/elements/ContentModule.php(63): MetaModels\FrontendIntegration\Module\Filter->generate()
#10 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(490): Contao\ContentModule->generate()
#11 /wwwroot.wwwnew/system/modules/core/modules/ModuleArticle.php(184): Contao\Controller->getContentElement(Object(Contao\ContentModel), 'main')
#12 /wwwroot.wwwnew/system/modules/core/modules/Module.php(148): Contao\ModuleArticle->compile()
#13 /wwwroot.wwwnew/system/modules/core/modules/ModuleArticle.php(59): Contao\Module->generate()
#14 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(405): Contao\ModuleArticle->generate(false)
#15 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(229): Contao\Controller->getArticle(Object(Contao\ArticleModel), false, false, 'main')
#16 /wwwroot.wwwnew/system/modules/core/pages/PageRegular.php(138): Contao\Controller->getFrontendModule('0', 'main')
#17 /wwwroot.wwwnew/index.php(239): Contao\PageRegular->generate(Object(Contao\PageModel), true)
#18 /wwwroot.wwwnew/index.php(407): Index->run()
#19 {main}

Alexander

Does the following works?

SELECT *, 
       COUNT(partner_name)
  FROM partner
 WHERE partner_name IN (
              SELECT partner_name 
                FROM partner 
               WHERE partner_main=1
       )
GROUP BY 
       partner_name
HAVING COUNT(partner_name) > 1
UNION ALL
SELECT *, 
       COUNT(partner_name)
  FROM partner
 WHERE partner_name IN (
               SELECT partner_name 
                 FROM partner 
                WHERE partner_main=0
       )
GROUP BY 
       partner_name
HAVING COUNT(partner_name) = 1

Collected from the Internet

Please contact [email protected] to delete if infringement.