您如何进行多对多表外部联接?

本文介绍了您如何进行多对多表外部联接?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有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.