How do you do many to many table outer joins?

Question

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

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

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

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

Answer 1

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

untested

Answer 2

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

Tested on MySQL 5.0.51.

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

Answer 3

Working it out, you can start with your select. Don't include columns you don't want to ultimately see.

SELECT foo.fid, bar.value

Then we can do the WHERE clause, which can see is just as you expressed it.

SELECT foo.fid, bar.value
WHERE bar.zid = 30

Now the tricky part to connect things together for our FROM clause, using LEFT JOINs because we want to see every fid, whether or not there are intermediate matches:

SELECT foo.fid, bar.value
FROM foo
LEFT JOIN foo2bar ON foo.fid = foo2bar.fid
LEFT JOIN bar ON foo2bar.bid = bar.bid
WHERE bar.zid = 30 OR bar.zid IS NULL

Answer 4

If you're not getting back a row for fid = 3 then your server is broken.

This code should do what I think you want:

SELECT
    F.fid,
    SQ.value
FROM
    dbo.Foo F
LEFT OUTER JOIN
     (
     SELECT F2B.fid, B.value
     FROM dbo.Bar B
     INNER JOIN dbo.Foo2Bar F2B ON F2B.bid = B.bid WHERE B.zid = 30
     ) SQ ON SQ.fid = F.fid

Keep in mind that it is possible to get back two values for a fid if it is related to two bars with a zid of 30.

Answer 5

FWIW, the question isn't really about a many-to-many: this could quite simply be done as a union.

A real many-to-many in SQL is a CROSS JOIN