[thelist] [mysql] join

[Joshua Olson]

Kris,

Here's an explanation of what I meant:

You can perform a double inner join on table two thusly:

SELECT some_other_stuff, t1.name AS name1, t2.name AS name2
FROM table_one
INNER JOIN table_two t1
ON t1.id = table_one.id_one
INNER JOIN table_two t2
ON t2.id = table_one.id_two

There are a couple gotchas with this solution.

1.  If either id_one or id_two refer to ids that do not appear in table_two,
or are NULL, then the row from table_one will not be returned at all.  You
can use LEFT OUTER JOINs instead of INNER JOINs to correct this problem.

2.  As soon as you move to OUTER JOINS the join ordering suddenly becomes a
factor.  This means you may need to use parenthesis to ensure that the join
of table_two and table_two does not happen.  The join of table_one and the
first table_two should happen first, then that result should be joined with
the other table_two.  The parser SHOULD be able to figure this out since
both join conditionals reference table_one, but in some databases you may
need to be explicit.

Clear as mud?

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168