Help with MYSQL query

Question

Here are two overly simplified version of 2 tables I'm using:

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   |
+-------+-----------------------+

This is the query I'm running right now:

SELECT * FROM B WHERE user='jack';

But, now I need to get the following result

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

All records where user=jack from table B must be returned with the corresponding color matching photo_id.

How can this be done?

Answer 1

Untested, but here goes:

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

Answer 2

select * from B LEFT JOIN A using(photo_id) where B.user = 'jack';