CODEWITHSUNDEEP
mysqlsql
M Shahzad Khan
M Shahzad Khan

Reputation: 935

find multiple records on basis of multiple fields in mysql table

I have mysql table of products. Which have field names like id, name, sku, programme,buyurl etc. Now I want to find duplicate record on the basis of sku but with the different programme name. I am actually doing it for price compare feature. Here is my code for finding duplicate records, which is fine but my problem is it have same programme names, and I need it for different.

SELECT  id, name, products.sku FROM products
INNER JOIN (SELECT sku FROM products
GROUP BY sku HAVING count(sku) > 1) dup ON products.sku= dup.sku

Sample data:

id | name | sku | programme | buyurl
 1 | pro1 | 123 | KFC       | kfc.com
 2 | pro2 | 123 | McDonald  | mcdonald.com
 3 | pro3 | 456 | McDonal   | mcdonald.com
 4 | pro4 | 123 | KFC       | kfc.com

Required output should be

 id | name | sku | programme | buyurl
 1  | pro1 | 123 | KFC       | kfc.com
 2  | pro2 | 123 | McDonald  | mcdonald.com

As there sku are same and programme are different.

Any help will be appreciated.

Upvotes: 1

Views: 342

Answers (2)

peterm
peterm

Reputation: 92785

You can try

SELECT p.id, name, sku, programme, buyurl
  FROM
(
  SELECT MIN(t1.id) id
    FROM products t1 JOIN products t2
      ON t1.sku = t2.sku 
     AND t1.programme <> t2.programme
   GROUP BY t1.sku, t1.programme
) q JOIN products p
    ON q.id = p.id

Output:

| ID | NAME | SKU | PROGRAMME |       BUYURL |
|----|------|-----|-----------|--------------|
|  1 | pro1 | 123 |       KFC |      kfc.com |
|  2 | pro2 | 123 |  McDonald | mcdonald.com |

Here is SQLFiddle demo

Upvotes: 2

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

If I understood you correctly, this query would work for you:

SELECT p1.id, p1.name, p1.sku, p1.programme
FROM product p1 JOIN product p2
ON p1.sku = p2.sku AND p1. programme <> p2.programme

If not then please add some sample data and expected result.

Based on sample data, try this query:

SELECT * FROM product
WHERE id IN(
   SELECT p1.id
   FROM (SELECT MIN(id) as id, sku, programme FROM product GROUP BY sku, programme) p1 
   JOIN (SELECT MIN(id) as id, sku, programme FROM product GROUP BY sku, programme) p2 
   ON p1.sku = p2.sku AND p1.programme <> p2.programme
)

Upvotes: 3

Related Questions