Find products with duplicate attribute value

I am trying to find out products with duplicate values on the basis of any specific attribute. Suppose I have an attribute MPN, and have to get all the products which are sharing the same MPN. I have designed a query which partially works but I found that it calls few products that were unique with attribute value.

select e.entity_id as ID,n.value as name,e.sku as sku,m.value as mpn from `catalog_product_entity` as e
        left join `catalog_product_entity_varchar` as m
        on e.entity_id = m.entity_id and m.attribute_id=156
        left join `catalog_product_entity_varchar` as n
        on e.entity_id = n.entity_id and n.attribute_id=71 
        group by m.value having count(*)> 1 order by e.entity_id asc

seems my logic is not fair enough to get what i want.

Any database Guru to help me out?

Answers


I have resolved this issue by applying another query to filter out the results. this may not be a good solution but it helped me out.

after getting results from above query, I used a foreach loop to find if the attribute value is unique or not.

  foreach($QueryCollection as $data){
      $query1 = "select * from catalog_product_entity_varchar as cpev where cpev.attribute_id=156 and cpev.value='".$data['mpn']."'";
      $IsDuplicate = $_conn2->fetchAll($query1);
      if(count($IsDuplicate)>1){
          //found this as duplicate
      }
  }

I have very less products around 200, so it was Ok for me, but I guess this is not good for higher counts.


Need Your Help

asp.net get date from yyMMdd

c# html asp.net

I have this small code here:

Freemarker - Is there a way to format an integer as a floating point number?

java freemarker

That's pretty much the question. I just want to do something basic like

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.