How to Perform Join Queries on Different Types of Databases
Problem description & analysis
There is a data table persons in MySQL, as shown below:
| Id_P |
LastName |
FirstName |
Address |
City |
| 1 |
Adams |
John |
Oxford Street |
London |
| 2 |
Bush |
George |
Fifth Avenue |
New York |
| 3 |
Carter |
Thomas |
Changan Street |
Beijing |
There is a data table orders in Oracle, as shown below:
| Id_O |
OrderNo |
Id_P |
| 1 |
77895 |
3 |
| 2 |
44678 |
3 |
| 3 |
22456 |
1 |
| 4 |
24562 |
1 |
| 5 |
34764 |
65 |
We are trying to list all people and all orders in a corresponding way. Below is the desired result:
| LastName |
FirstName |
OrderNo |
| Adams |
John |
22456 |
| Adams |
John |
24562 |
| Carter |
Thomas |
77895 |
| Carter |
Thomas |
44678 |
| Bush |
George |
|
| 34764 |
Solution
Write the following script p1.dfx in esProc:
| A |
|
| 1 |
=connect("mysql") |
| 2 |
=A1.query@x("select * from persons") |
| 3 |
=connect("oralce") |
| 4 |
=A3.query@x("select * from orders") |
| 5 |
=join@f(A2:persons,id_p;A4:orders,id_p).new(persons.lastname,persons.firstname,orders.orderno) |
Explanation:
A1 Connect to MySQL database.
A2 Get data from persons table.
A3 Connect to Oracle database.
A4 Get data from orders table.
A5 Perform a full join on A2 and A4 by id_p to list all desired data.
Read How to Call an SPL Script in Java to learn how to integrate the script code into a Java program.
https://stackoverflow.com/questions/50597874/is-it-possible-to-write-cross-database-join-queries
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
