converting joins from oracle to mysql

[deepak.rao]

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]

de********@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.