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

Solution 1

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.

Solution 2

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

untested

Solution 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

Solution 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.

View more solutions

Comments

[Pyrolistical]

you have a typo, but it does work. is the subquery bad for performance?

[Tjkoopa]

As with Bill Karwin's, it not a subquery

[dkretz]

Actually, the parens are optional, as I'm sure Bill knows.

[Tjkoopa]

That shouldn't work as it will not give rows that have a null zid (foo rows that don't map to a bar row)

[dkretz]

Bill, I think that may not work if there is a foo2bar record with no matching bar record, since it's an inner join internally? But I never use " ON " and " USING " in any case, so that's speculation. :)

[Bill Karwin]

Parens are optional of course, but they do allow you to override default precedence. Just like in arithmetic expressions: 10*10+10 equals 110, whereas 10*(10+10) equals 200.

[Pyrolistical]

You are correct, I messed up my example, fid 3 row does exist

[Bill Karwin]

@le dorfier: the OP said foo2bar is an intersection table for a many-to-many relationship, so there should always be a row in bar matching a given row in foo2bar.

[dkretz]

The sure sign of a C programmer, assuming single-pass compilation. :)

[Bill Karwin]

@Pyrolistical: good point, that's because JOIN syntax is not purely infix notation like the arithmetic syntax example. So adding parens changes the order of terms.

[Pyrolistical]

Ugh, you are also correct in that if there are two bars with zid of 30 related to the same foo, there will be issues. My schema doesn't prevent that either...

[dkretz]

@bill, I don't think you can even write that into a referential integrity assertion, can you? But I'm willing to concede a perfect world for an academic exercise.

[Bill Karwin]

@le dorfier: bid NOT NULL, FOREIGN KEY (bid) REFERENCES bar(bid)

[dkretz]

Conceded. I must have missed when MS implemented that. Another arrow in my quiver - thanks. Handy - it allows for zid NULL without requiring you to express it in your query.