[thelist] [mysql] join

[Joshua Olson]

----- Original Message ----- 
From: "Tab Alleman" <Tab.Alleman at MetroGuide.com>
Sent: Wednesday, January 28, 2004 11:00 AM

> Does MySQL not support "OR" operators in the Search condition of a JOIN?

> Could one not do:

> 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
> OR t2.id = table_one.id_two

Tab,

I'm sure that MySQL does indeed support the OR operator in the JOIN syntax.
However, the usage of it probably will not lead to the solution that we are
looking for.  In the query you suggested above, the t2 table is never
referenced... indeed the only tables in use are table_one and table_two
(which is aliased to t1).  Given that, the SELECT field list will not work.

>From a logical perspective, the reduction of this problem to a simple "OR"
creates other issues.  I'll try to explain.

With only one inner join we are saying that we need to compare table_one
against every row in table_two only once, and then discard those rows that
do not match the "on" criteria.  So, we will not be able to return in the
result a single row that contains information from one record of table_one
and two separate records from table_two.

By performing two inner joins we are independently looking up both records
from table_two that match the values from table_one.

Make more sense?

(where's rudy when ya need him?)  :-)

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