如果两个行在列中具有相同的值并且前五个字符相同，则过滤自联接查询

I executed the following query using self-join

我使用自联接执行了以下查询

SELECT 
    a.Product_ID , b.Parent_Product_ID, b.Product_Name 
    FROM Product a, Product b
    where a.Product_ID = b.Parent_Product_ID

This is the result

结果就是这样

Product_ID  Parent_Product_ID Prodcut_Name 
311        311             Trench
353        353             Blended wool
353        353             Blended wool polyester
355        355             Faux fur
357        357             Quilted 
358        358            Jackets-Polyester
359        359            Jackets-Wool

I am trying to filter above result by

我试图过滤上面的结果

a) Product_ID = Parent_Prodcut_ID if both rows have same values in the columns. Here the result should be

a）如果两列中的行具有相同的值，则Product_ID = Parent_Prodcut_ID。结果应该是

353        353             Blended wool
353        353             Blended wool polyester

b) If the first five characters in Product_name are same, here the result should be

b）如果Product_name中的前五个字符相同，则结果应为

353        353             Blended wool
353        353             Blended wool polyester
358        358            Jackets-Polyester
359        359            Jackets-Wool

1 个解决方案

#1

Just add these conditions to the on clause (oh, your query should have that too):

只需将这些条件添加到on子句中（哦，你的查询也应该有）：

SELECT a.Product_ID , b.Parent_Product_ID, b.Product_Name 
FROM Product a JOIN
     Product b
     ON a.Product_ID = b.Parent_Product_ID AND
        a.product_name = b.product_name;

And for the second:

而对于第二个：

SELECT a.Product_ID , b.Parent_Product_ID, b.Product_Name 
FROM Product a JOIN
     Product b
     ON a.Product_ID = b.Parent_Product_ID AND
        LEFT(a.product_name, 5) = LEFT(b.product_name, 5);

If your database doesn't have a LEFT() function, use SUBSTR()/SUBSTRING() instead.

如果您的数据库没有LEFT（）函数，请改用SUBSTR（）/ SUBSTRING（）。

#1