MySQL 选择不同的值并选择更多

Question

我有一个表，其中包含指定的 id、value1、value2 和 value3

MyTable1 看起来像这样:

+------+--------+--------+--------+
| ID   | VALUE1 | VALUE2 | VALUE3 |
+------+--------+--------+--------+
| 1    | 103    | 202    | 3212   |
| 2    | 103    | 202    | 7667   |
| 3    | 103    | 202    | 7567   |
| 4    | 103    | 203    | 6545   |
| 5    | 103    | 203    | 9787   |
| 6    | 106    | 203    | 6545   |
| 7    | 106    | 203    | 6575   |
| 8    | 106    | 202    | 8909   |
| 9    | 106    | 206    | 4656   |
| 10   | 106    | 205    | 5676   |
| 11   | 106    | 208    | 9088   |
| 12   | 107    | 202    | 8998   |
| 13   | 107    | 205    | 5675   |
| 14   | 107    | 204    | 9787   |
| 15   | 107    | 204    | 6454   |
| 16   | 107    | 206    | 9089   |
| 17   | 107    | 202    | 1233   |
| 18   | 107    | 205    | 8765   |
| 19   | 108    | 208    | 8900   |
| 20   | 103    | 205    | 6543   |
+------+--------+--------+--------+

MyTable2 看起来像这样:

+------+--------+--------+---------+
| ID   | VALUE1 | VALUE2 | STATUS  |
+------+--------+--------+---------+
| 1    | 103    | 209    | OK      |
| 2    | 105    | 203    | DEAD    |
| 3    | 107    | 205    | GOOD    |
| 4    | 104    | 202    | WRONG   |
| 5    | 103    | 203    | WOW     |
+------+--------+--------+---------+

当我想要结果时。我正在使用此代码。

SELECT DISTINCT value1,value2 FROM MyTable1 WHERE value1 = 103

结果如下所示。

+--------+---------+
| VALUE1 | VALUE2  |
+--------+---------+
| 103    | 202     |
| 103    | 203     |
| 103    | 205     |
+--------+---------+

但是我想添加一个状态列，这就是 MyTable2

我试过类似的东西，但我没有得到结果。

SELECT DISTINCT MyTable1.value1,MyTable1.value2,MyTable2.status FROM MyTable1,MyTable2 WHERE MyTable1.value1 = 103 AND MyTable2.value1 = MyTable1.value1 AND MyTable2.value2 = MyTable1.value2

无论如何，我想要这个结果。

+--------+--------+---------+
| VALUE1 | VALUE2 | STATUS  |
+--------+--------+---------+
| 103    | 202    |         |
| 103    | 203    | WOW     |
| 103    | 205    |         |
+--------+--------+---------+

谢谢!!!

Answer 1

像这样的东西应该可以工作:

SELECT DISTINCT 
    tb1.value1,
    tb1.value2, 
    tb2.status
FROM 
    table1 tb1 
LEFT JOIN
   table2 tb2 on tb1.value1 = tb2.value1
   and tb1.value2 = tb2.value2
WHERE 
  tb1.value1 = 103