mysql - SQL 将列的值与其他列的所有值匹配

Question

我只有一张表:产品

ID | Product Name | Code 1 | Code 2 | Code 3
********************************************
1  |    Phone1    |   MM   |   DD   |  
2  |    Phone2    |   DD   |        |   EE
3  |    Phone3    |   MM   |   ZZ   |   EE
4  |    Phone4    |   XX   |        |

我愿意:

select all phones which one of their codes match one of the Phone1's code.

换句话说:

if Phone1[Code 1] = Phone2[Code 1] OR Phone1[Code 1] = Phone2[Code 2] OR Phone1[Code 1] = Phone2[Code 3] OR Phone1[Code 1] = Phone3[Code 1] ... etc

将每个Phone1 列的值 与每个PhoneX 列的值 进行比较。 3x3 条件 = 总共 9 个。

所以它应该返回:Phone2 和 Phone3(它应该跳过 empty = empty 的匹配项)。

我的尝试(SELF JOIN)——我是新手，做错了:

SELECT `a.Product Name`
FROM products a, products b
WHERE 
(`a.Code 1` = `b.Code 1`) OR 
(`a.Code 1` = `b.Code 2`) OR   
(`a.Code   1` = `b.Code 3`) OR 
(`a.Code 2` = `b.Code 1`) OR 
(`a.Code 2` = `b.Code 2`) OR 
(`a.Code 2` = `b.Code 3`) OR
(`a.Code 3` = `b.Code 1`) OR
(`a.Code 3` = `b.Code 2`) OR
(`a.Code 3` = `b.Code 3`)
AND `a.Product Name` = "Phone1";

提前致谢!

Answer 1

也许是这样的。

SELECT  p.*
FROM    Products p
        JOIN Products p1 ON p1.`Product Name` = 'Phone1'
                            AND (p1.`Code 1` IN (p.`Code 1`,p.`Code 2`,p.`Code 3`)
                                 OR p1.`Code 2` IN (p.`Code 1`,p.`Code 2`,p.`Code 3`)
                                 OR p1.`Code 3` IN (p.`Code 1`,p.`Code 2`,p.`Code 3`))
WHERE   p.`Product Name` <> 'Phone1'

SQL Fiddle Demo