Hello,

I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like:

select ...
from...
where
....
and PT1.ID (+) = bl.PARENTTYPE_1
and PT2.ID (+) = bl.PARENTTYPE_2
....


MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted.

example:
select ...
from
tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1),
tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2),
....

-> doesnt' work.


Exits a solution for this example? Other syntax possibilities?


regards,
Spiker
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger

spikerlion@gmx.de wrote:
> Hello,
>
> I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like:
>
> select ...
> from...
> where
> ...
> and PT1.ID (+) = bl.PARENTTYPE_1
> and PT2.ID (+) = bl.PARENTTYPE_2
> ...
>
>
> MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the same table aren't accepted.
>
> example:
> select ...
> from
> tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1),
> tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2),

The exact error message would be ful, but I'm seeing at least two
problems:

1) you're aliasing two tables as 'bl'. The aliases need to be unique.
2) The second ON clause shouldn't start with AND.

Otherwise you should have no problem doing this.

Hello,

thank you - now it works.

d_parenttype PT1 LEFT OUTER JOIN t_booklists bl ON (PT1.ID = bl.PARENTTYPE_1),
d_parenttype PT2 LEFT OUTER JOIN t_booklists bk ON (PT2.ID = bk.PARENTTYPE_2)


I had to put the alias to all listet fields in the select.


regards,
Spiker



-------- Original-Nachricht --------
> Datum: Fri, 31 Aug 2007 09:30:13 -0400
> Von: Baron Schwartz <baron@xaprb.com>
> An: spikerlion@gmx.de
> CC: mysql@lists.mysql.com
> Betreff: Re: JOIN migration from Oracle to MySQL

> spikerlion@gmx.de wrote:
> > Hello,
> >
> > I've two LEFT OUTER JOINS in the WHERE section in an Oracle script like:
> >
> > select ...
> > from...
> > where
> > ...
> > and PT1.ID (+) = bl.PARENTTYPE_1
> > and PT2.ID (+) = bl.PARENTTYPE_2
> > ...
> >
> >
> > MySQL knows LEFT OUTER JOINS in the FROM section but two joins with the
> same table aren't accepted.
> >
> > example:
> > select ...
> > from
> > tableA PT1 LEFT OUTER JOIN tableC bl ON (PT1.ID = bl.PARENTTYPE_1),
> > tableB PT2 LEFT OUTER JOIN tableC bl ON (and PT2.ID = bl.PARENTTYPE_2),
>
> The exact error message would be ful, but I'm seeing at least two
> problems:
>
> 1) you're aliasing two tables as 'bl'. The aliases need to be unique.
> 2) The second ON clause shouldn't start with AND.
>
> Otherwise you should have no problem doing this.

--
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser

spikerlion@gmx.de wrote:
> Hello,
>
> thank you - now it works.
>
> d_parenttype PT1 LEFT OUTER JOIN t_booklists bl ON (PT1.ID = bl.PARENTTYPE_1),
> d_parenttype PT2 LEFT OUTER JOIN t_booklists bk ON (PT2.ID = bk.PARENTTYPE_2)
>
>
> I had to put the alias to all listet fields in the select.
>

Unless you are relating PT1 to PT2 in some way, you should not expect
this query to perform well because you will be generating a Cartesian
product between PT1 and PT2. I doubt this is actually what you are
trying to do (although it will eventually work). If you posted just a
few more details about the query you are trying to write, we could try
to you to rewrite it in a way that will perform much better than
the translation you just attempted.
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html