By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,758 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,758 IT Pros & Developers. It's quick & easy.

Mysql's equivalent to Oracle's (+) for null values

P: n/a
Hi i'm working on a web project, and i would like to make my oracle
query work in mysql.

select match.numero "nummatch",
to_char(match.datematch, 'yyyy-MM-dd') "datematch",
p1.numjoueur "j1",
p2.numjoueur "j2",
po1.p1 "m1p1",
po1.p2 "m1p2",
po2.p1 "m2p1",
po2.p2 "m2p2",
po3.p1 "m3p1",
po3.p2 "m3p2"
from participant p1,
participant p2,
match,
point po1,
point po2,
point po3
where p1.nummatch(+) = match.numero and
p2.nummatch(+) = match.numero and
po1.nummatch(+) = match.numero and
po2.nummatch(+) = match.numero and
po3.nummatch(+) = match.numero and
match.type = 'D' and
p1.position(+) = 1 and
p2.position(+) = 2 and
po1.manche(+) = 1 and
po2.manche(+) = 2 and
po3.manche(+) = 3 and
p1.numjoueur = 1;
i can't seem to find an equivalent to Oracle's (+) for null values in
MYSQL.

Any help is appreciated, thanks
-John
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
JBBHF wrote:
Hi i'm working on a web project, and i would like to make my oracle
query work in mysql.
I don't have a lot of experience with Oracle, but my understanding is
that the (+) syntax is Oracle's pre-SQL92 solution to implement what
eventually became standardized with the syntax OUTER JOIN.

MySQL pretty much uses the ANSI/ISO SQL92 standard syntax for outer
joins. You could probably benefit from a SQL book on writing queries
including outer joins. No need for it to be specific to MySQL.
select match.numero "nummatch", .. . . from participant p1,
participant p2,
match,
point po1,
point po2,
point po3
where p1.nummatch(+) = match.numero and
p2.nummatch(+) = match.numero and
po1.nummatch(+) = match.numero and
po2.nummatch(+) = match.numero and
po3.nummatch(+) = match.numero and
match.type = 'D' and
p1.position(+) = 1 and
p2.position(+) = 2 and
po1.manche(+) = 1 and
po2.manche(+) = 2 and
po3.manche(+) = 3 and
p1.numjoueur = 1;


I think the following query would be equivalent:

SELECT match.numero AS nummatch, ...etc...
FROM match
LEFT OUTER JOIN participant p1 ON (match.numero = p1.nummatch AND
p1.position = 1)
LEFT OUTER JOIN participant p2 ON (match.numero = p2.nummatch AND
p2.position = 2)
LEFT OUTER JOIN point po1 ON (match.numero = po1.nummatch AND
po1.manche = 1)
LEFT OUTER JOIN point po2 ON (match.numero = po2.nummatch AND
po2.manche = 2)
LEFT OUTER JOIN point po3 ON (match.numero = po3.nummatch AND
po3.manche = 3)
WHERE match.type = 'D' AND p1.numjoueur = 1

Regards,
Bill K.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.