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.

Q & A Collection

https://stackoverflow.com/questions/50597874/is-it-possible-to-write-cross-database-join-queries