本文介绍了您如何进行多对多表外部联接?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有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.
更多推荐
您如何进行多对多表外部联接?
发布评论