Have you tried using the IN operator?
SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk,
degeber, de_heid, acdesc, acfund
FROM (details INNER JOIN headers ON details.de_heid = headers.heid)
LEFT JOIN acctref ON acctref.acfund IN
(details.dekostl,details.deprojk,details.degeber)
WHERE headers.he_baid=38;
You could also split this into two steps. This would help you to work
around known issues with nested joins (see also: http://bugs.mysql.com/1677
and http://bugs.mysql.com/1591):
CREATE TEMPORARY TABLE tmpJoin1
SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk, degeber, de_heid
FROM details
INNER JOIN headers
ON details.de_heid = headers.heid
WHERE headers.he_baid=38
SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk,
degeber, de_heid, acdesc, acfund
FROM tmpJoin1
LEFT JOIN acctref ON acctref.acfund IN (dekostl,deprojk,degeber)
Another optimization could be to put your WHERE restriction into your ON
clause of the INNER JOIN. (That way the JOIN processing should match up
your tables on fewer total rows)
SELECT ...
FROM ...
INNER JOIN headers
ON details.de_heid = headers.heid
AND headers.he_baid=38
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Michael Ragsdale
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
> cc:
Fax to:
06/02/2004 09:13 Subject: left join with multiple OR?
AM
I have some code that's been in production for a few years and now is
presenting a problem. The following is on a mysql 3.23.38 server:
SELECT b.denewbs, b.dewrbtr, b.dekostl, b.desgtxt, b.deprojk,
b.degeber, b.de_heid, c.acdesdc, c.acfund
FROM headers a, details b, acctref c
WHERE a.he_baid = ? AND
b.de_heid = a.heid AND
(c.acfund = b.dekostl OR
c.acfund = b.deprojk OR
c.acfund = b.degeber)
ORDER by acfund;
Note: The ? is a placeholder for DBI.
My problem is this - there is a value that is placed in one, and only one,
of dekostl, deprojk, or degeber for each record and this value corresponds
to a value in acfund, thus the OR statements. However, some values have
recently been removed from acfund, affecting reports where these deleted
values are still in existence in the other fields, so I need to add a left
join, I suppose. Now in Oracle, I could simply use (+) in all three
places. In mysql, I know how to do this with one simple comparison, but
not with three.
I've tried the following and it didn't like the OR statements. It worked
with AND, but of course, there was no acdesc or acfund in the output
because it didn't match all three conditions - the acfund is only in one of
dekostl, deprojk or degeber - and which one is not known for each record,
so I need to check all three.
SELECT denewbs, dewrbtr, dekostl, desgtxt, deprojk,
degeber, de_heid, acdesc, acfund
FROM (details INNER JOIN headers ON details.de_heid = headers.heid)
LEFT JOIN acctref ON ((details.dekostl = acctref.acfund) OR
(details.deprojk = acctref.acfund) OR
(details.degeber = acctref.acfund))
WHERE (((headers.he_baid)=38));
at a loss,
-Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
