|
Home > Archive > MySQL Server Forum > June 2005 > converting joins from oracle to mysql
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
converting joins from oracle to mysql
|
|
| deepak.rao@gmail.com 2005-06-30, 9:23 am |
| Hi,
I have to run the following statement in MySQL. (The script is
generated from oracle).
*************
SELECT t1.c1, t2.c2, t3.c3
from t1, t2, t3
where t1.flag = 1
and (t1.cx = t2.cx(+) and t2.cy(+) = 1)
and ( (t2.cz(+) = t3.cz and not exists (select * from t3) )
or
(t1.c3 = 2)
)
;
****************
This looks a bit complex for me as I am not familiar with JOINs. It
would be great if someone can help me out.
Thanks,
Deepak
| |
| Bill Karwin 2005-06-30, 1:23 pm |
| deepak.rao@gmail.com wrote:
> Hi,
>
> I have to run the following statement in MySQL. (The script is
> generated from oracle).
>
> *************
> SELECT t1.c1, t2.c2, t3.c3
> from t1, t2, t3
> where t1.flag = 1
> and (t1.cx = t2.cx(+) and t2.cy(+) = 1)
> and ( (t2.cz(+) = t3.cz and not exists (select * from t3) )
> or
> (t1.c3 = 2)
> )
> ;
The join syntax using "(+)" is specific to Oracle and does not match any
SQL standard. The equivalent in standard SQL is to use OUTER JOIN.
That join condition makes no sense from what I can tell. "not exists
(select * from t3)" is true only if the t3 table is empty. If that is
true, how can any row exist where t2.cz = t3.cz? Therefore that term in
the expression is guaranteed to be false, so it can be factored out,
leaving the other side of the "OR" expression.
The best I can guess for this is the following:
SELECT t1.c1, t2.c2, t3.c3
FROM t1 LEFT OUTER JOIN t2 ON (t1.cx = t2.cx AND t2.cy = 1)
JOIN t3 ON (t1.c3 = 2)
WHERE t1.flag = 1;
The condition (t1.c3 = 2) is highly unusual and likely not to be what
you intended for this query. It is more usual for a join condition to
describe a relationship between two tables.
> This looks a bit complex for me as I am not familiar with JOINs. It
> would be great if someone can help me out.
You should pick up a book or find a tutorial on the web. JOINs are not
difficult to understand, but they are crucial to writing queries.
Programming in SQL without understanding JOINs is like programming in C
without understanding functions.
Regards,
Bill K.
|
|
|
|
|