MYSQL SELECT Group By Statement

Question

Products Table:

ProductID | Manufacturer  | Part No     | Name
-----------------------------------------------------------------
770593    | GBC           | EB100000    | GBC (30 inch) ID Neck Chain - 1 x Pack of 100 Neck Chains
775308    | Elba          | 100080883   | Elba Vision (A5) Ring Binder
801982    | Elba          | 100080883   | Elba Vision (A5) Ring Binder

Matched Skus Table:

SupplierSKUID | ProductID
--------------|-------------------
Sku1          | 770593 
Sku2          | 770593 
Sku3          | 775308
Sku4          | 775308
Sku5          | 801982

Result:

SupplierSKUID | ProductID
--------------|-------------------
Sku1          | Sku2 
Sku2          | Sku1 
Sku3          | Sku4
Sku3          | Sku5
Sku4          | Sku3
Sku4          | Sku5
Sku5          | Sku3
Sku5          | Sku4

From the above example you can see that Sku1 and Sku2 are same. Also Sku3, Sku4 and Sku5 are same. Sku5 is same as Sku3 and Sku4 because it has same name, manufacturer and part no in products table as ProductID 775308 even though the ProductID is different.

I need to group similar skus in matched skus table using ProductID as well as Name in products table.

Answer 1

You can do this:

SELECT
  Name,
  GROUP_CONCAT(ProductId) AS LinkedProducts,
  GROUP_CONCAT(SupplierSKUID) AS LinkedSKUS
FROM
(
    SELECT
      s.ProductId,
      p.Name,
      s.SupplierSKUID
    FROM Products AS p
    INNER JOIN SKus AS s ON p.ProductID = s.ProductID 
    UNION
    SELECT
      s.ProductId,
      p1.Name AS Name1,
      s.SupplierSKUID
    FROM Products AS p1
    INNER JOIN Products AS p2  ON p1.Name = p2.Name 
                              AND p1.ProductID <> p2.ProductID 
    INNER JOIN SKus AS s ON p1.ProductID = s.ProductID 
) AS t
GROUP BY Name;

This will give you product name, with all the linked SKUs and all the linked Product Ids across the two tables:

|                                                      Name |       LinkedProducts |     LinkedSKUS |
|-----------------------------------------------------------|----------------------|----------------|
|                              Elba Vision (A5) Ring Binder | 801982,775308,775308 | Sku5,Sku4,Sku3 |
| GBC (30 inch) ID Neck Chain - 1 x Pack of 100 Neck Chains |        770593,770593 |      Sku1,Sku2 |

• SQL Fiddle Demo

The first unioned query will give matched products ids from products table and skus table. And the second unioned query will give matched products names within the same table and in the same time match them against the skus table.