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.d atematch, '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 1 5656
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mark Wilson CPU |
last post by:
A colleague has written a prototype program in PHP, using a MySQL
database.
It's a relatively simple app, with a restricted set of mysql commands
used (see below). The MySQL DB is being replaced with an Oracle DB
(same schema). My plan
1) globally replace the few mysql commands with intermediate
equivalents (such as myDB_connect for mysql_connect)
2) those central functions would then (for now) call the original
mysql function to prove...
|
by: Asfand Yar Qazi |
last post by:
Ahem..
Anyway, here's whats happening...
construct tables in MySQL:
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO INT(4) NOT NULL,
ENAME CHAR(6) NOT NULL,
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest version of this document can be found at:
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
|
by: jonathan |
last post by:
hey all,
I realize that this question might pop up from time to time, but I
haven't seen it a while and things might of changed, so -
Right now (July 2004) how does mysql stand up in comparison to oracle?
We are seriously considering migrating our multi-processor oracle
system to mysql to save on licensing costs, and would need several
features that mysql may or may not have:
|
by: Cern |
last post by:
Is it somebody out there who has made a migration from an Oracle server to an MySQL server??
The scenario is as simply:
I've got a Oracle 8 server with a database with content that I want to transfer to
a MySQL database. No special data, constraints etc that MySQL not will handle.
My solution is to reverse engineer the database from ERStudio and then produce a SQL script that
will insert the data into the MySQL engine.
But I can't do...
| |
by: KingGreg |
last post by:
All,
Oracle 9i provides a "USING" clause option for inner joins, that
allows me to say:
SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
for our software make use of, but we also support SQL Server. There
is no USING option available, and
|
by: smorrey |
last post by:
I've been thinking on this long and hard, and I can't seem to come up
with an answer on it.
Why is it almost always assumed the MySQL will be the server for nearly
any PHP app?
Why is it MySQL and not PostGRES or SQLite?
At this point the only reason I can think of is that MySQL has a much
more friendly name. But is that really it?
|
by: Andrew S |
last post by:
Hello Mr. Expert:
- I have 3 tables in mysql in MyISAM table format, I am using mysql4.0
on freebsd5.3
- producttbl, productdetailentbl, pricetblN
- they all have "productid" as the Primary KEY. there are less than
300,000 records in each table
- when I ran the statement
select productname from productdetailentbl left join producttbl on
productdetailentbl.productid=producttbl.productid left join pricetblN
on...
|
by: Atli |
last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users.
Anyone should be able to get through this without much trouble. Programming knowledge is not required.
Index
What is SQL?
Why MySQL?
Installing MySQL.
Using the MySQL command line interface
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |