sql – 你如何做许多到多个表外连接？

我有3个表,foo,foo2bar和bar. foo2bar是foo和bar之间的很多地图.这里是内容.

SELEct * from foo
+------+
| fid  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

SELEct * from foo2bar
+------+------+
| fid  | bid  |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    1 |
|    2 |    3 |
|    4 |    4 |
+------+------+

SELEct * from bar
+------+-------+------+
| bid  | value | zid  |
+------+-------+------+
|    1 |     2 |   10 |
|    2 |     4 |   20 |
|    3 |     8 |   30 |
|    4 |    42 |   30 |
+------+-------+------+

我想要的是,“给我一个所有的fid和值的列表,带有30”

我期待所有的fids的答案,所以结果将是：

+------+--------+
| fid  | value  |
+------+--------+
|    1 |   null |
|    2 |      8 |
|    3 |   null |
|    4 |     42 |
+------+--------+

SELECT * FROM foo
  LEFT OUTER JOIN (foo2bar JOIN bar ON (foo2bar.bid = bar.bid AND zid = 30))
  USING (fid);