MySQL选择Product-SimilarProduct

Question

I have two tables products and similar_products. Products tables holds data related to available products however this tables has loads of products that are same but with different product id. Which is why I have another table that hold the data for all the similar products. I need to run a query that selects products from products table but at the same also check in similar_products tables to not select any duplicate products.

So for example:

Products Table:

ProductID | Manufacturer  | Part No     | Name
-----------------------------------------------------------------
8202      | Hp            | 402146-B21  | HP Auto Synch Cable
8210      | Hp            | 113894-B21  | HP Stylus 3 Pack
8211      | Hp            | 113894-B21  | HP Stylus 3 Pack
8212      | Hp            | 113894-B21  | HP Stylus 3 Pack

Similar_products Table

ProductID | Similar_ProductID
----------|-------------------
8210      | 8211
8210      | 8212
8211      | 8210
8211      | 8212
8212      | 8210
8212      | 8211

How can I run a query that will only select ProductID 8202 and 8210 and not select duplicates products.

Answer 1

In case you always have the relationship x~y as the two records [x,y] and [y,x] in Similar_products your problem is a variation of The Rows Holding the Group-wise Maximum of a Certain Column

SELECT
    p.ProductID, p.Name
FROM
    Products as p
WHERE
    NOT EXISTS(
        SELECT
            1
        FROM
            Similar_products s
        WHERE
            p.ProductID=s.ProductID
            AND p.ProductID>s.Similar_ProductID
    )