SEO

#1 (**permalink**) 02-28-2008, 07:00 AM

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

#2 (**permalink**) 02-28-2008, 07:00 AM

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 helpful, 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.

#3 (**permalink**) 02-28-2008, 07:00 AM

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 helpful, 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

#4 (**permalink**) 02-28-2008, 07:00 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode