MYSQL 查询 : All records where user=jack from table `B` must be returned with the corresponding color matching `photo_id`

Question

这是我正在使用的 2 个表的两个过于简化的版本:

A:
+-------+-----------------------+ 
| id    | photo_id     | color  |
+-------+-----------------------+ 
| 1     | 100          | red    |
| 2     | 101          | blue   |
| 3     | 102          | green  |
+-------+-----------------------+

B:
+-------+-----------------------+ 
| id    | photo_id     | user   |
+-------+-----------------------+ 
| 1     | 100          | jack   |
| 2     | 101          | jill   |
| 3     | 102          | jack   |
| 4     | 103          | jill   |
| 5     | 104          | jack   |
| 6     | 105          | jack   |
| 7     | 106          | jack   |
+-------+-----------------------+

这是我现在正在运行的查询:

SELECT * FROM B WHERE user='jack';

但是，现在我需要得到以下结果

C:
+-------+-----------------------+--------+  
| id    | photo_id     | user   | color  |
+-------+-----------------------+--------+  
| 1     | 100          | jack   | red    |
| 2     | 102          | jack   | blue   |
| 3     | 104          | jack   | green  |
| 4     | 105          | jack   |        |  
| 5     | 106          | jack   |        |
+-------+-----------------------+--------+

表 B 中 user=jack 的所有记录必须返回相应的颜色匹配 photo_id。

如何做到这一点？

Answer 1

未经测试，但这里是:

SELECT * FROM B LEFT JOIN A ON A.photo_id = B.photo_id WHERE user = 'jack'