PHWinfo - Afficher un message

31/08/2007, 14h30

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.

31/08/2007, 14h30	#2
Baron Schwartz Messages: n/a Hébergeur:	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.