您如何进行多对多表外部联接? [英] How do you do many to many table outer joins?

问题描述

我有3个表foo，foo2bar和bar. foo2bar是foo和bar之间的多对多映射.这是内容.

I have 3 tables, foo, foo2bar, and bar. foo2bar is a many to many map between foo and bar. Here are the contents.

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

我要请求的是，给我一个zid为30的所有fid和值的列表"

What I want to request is, "Give me a list of all the fid and values with zid of 30"

我希望所有fid都有答案，所以结果看起来像这样:

I expect an answer for all the fids, so the result would look like:

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

在MySQL 5.0.51上进行了测试.

Tested on MySQL 5.0.51.

这不是子查询，它仅使用括号指定连接的优先级.

This is not a subquery, it just uses parentheses to specify the precedence of joins.

这篇关于您如何进行多对多表外部联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！