Join two tables in Oracle sql

Question

I have 2 tables. One of the tables has 7 values, and the other table has 5 values. These tables share common primary keys. I want to join both tables this way: If I have a table

English French -------------------- -------------------- one Un two Deux three Trois four Quatre four Quattro five Cinq five Cinco 

And one more:

 English French -------------------- -------------------- one aaaaa two bbbbb three ccccc four five 

I want to have a table like this:

 English French -------------------- -------------------- one Un one aaaaa two Deux two bbbb three Trois three ccccc four Quatre four Quattro four -------- five Cinq five Cinco five ---------- 

I tried using join, but it does a linear combination of four and five . How can i do this? Thanks.

Edit : SQL query:

 SELECT l.date_location, l.duree, r.km_restitution, r.km_parcouru FROM locations l, restitutions r UNION SELECT l.num_client, l.date_location, l.duree, r.km_restitution, r.km_parcouru FROM locations l, restitutions r id_agence num_immatriculation num_client km_restitution km_parcouru state date_restitution 1 406BON69 1002 30000 1000 BON 29-MAY-10 3 785CIM13 1001 56580 80 BON 09-AUG-08 5 800BBB75 1000 2020 20 BON 24-APR-11 4 307VXN78 1000 20040 40 BON 28-JAN-11 2 290UTT92 1004 30030 30 BON 01-AUG-10 5 777SET13 1005 4030 30 BON 26-APR-11 2 179CLV92 1004 15015 15 BON 03-FEB-11 5 400AAA75 1003 1020 20 BON 18-SEP-11 5 666NEF69 1004 3040 40 BON 15-APR-11 2 111AAA75 1001 20020 20 BON 21-DEC-09 1 333CCC78 1001 43250 40 BON 27-DEC-09 2 260CDE95 1003 79000 430 BON 10-SEP-09 4 307VXN78 1003 20090 90 BON 11-FEB-11 1 123ABC78 1003 10010 10 BON 04-OCT-10 1 222BBB77 1001 9050 50 BON 23-DEC-09 Locations id_agence num_immatricul num_client duree date_location 2 406BON69 1002 20 10-MAY-10 3 785CIM13 1001 3 07-AUG-08 5 800BBB75 1000 7 18-APR-11 4 307VXN78 1000 5 24-JAN-11 1 290UTT92 1004 1 31-JUL-10 5 777SET13 1005 4 23-APR-11 1 179CLV92 1004 5 30-JAN-11 5 400AAA75 1003 2 17-SEP-11 2 123ABC78 1003 4 01-OCT-10 5 666NEF69 1004 5 11-APR-11 1 111AAA75 1001 2 20-DEC-09 1 222BBB77 1001 2 22-DEC-09 1 333CCC78 1001 3 25-DEC-09 1 260CDE95 1003 10 01-SEP-09 4 307VXN78 1003 13 30-JAN-11 2 123ABC78 1003 8 20-NOV-11 2 406BON69 1002 10 20-NOV-11 

Desired Result

 id_agence num_immatricul num_client duree date_location date_restitution 2 406BON69 1002 20 10-MAY-10 date_restitution 3 785CIM13 1001 3 07-AUG-08 date_restitution 5 800BBB75 1000 7 18-APR-11 date_restitution 4 307VXN78 1000 5 24-JAN-11 date_restitution 1 290UTT92 1004 1 31-JUL-10 date_restitution 5 777SET13 1005 4 23-APR-11 date_restitution 1 179CLV92 1004 5 30-JAN-11 date_restitution 5 400AAA75 1003 2 17-SEP-11 date_restitution 2 123ABC78 1003 4 01-OCT-10 date_restitution 5 666NEF69 1004 5 11-APR-11 date_restitution 1 111AAA75 1001 2 20-DEC-09 date_restitution 1 222BBB77 1001 2 22-DEC-09 date_restitution 1 333CCC78 1001 3 25-DEC-09 date_restitution 1 260CDE95 1003 10 01-SEP-09 date_restitution 4 307VXN78 1003 13 30-JAN-11 date_restitution 2 123ABC78 1003 8 20-NOV-11 ---------------- 2 406BON69 1002 10 20-NOV-11 --------------- 

In addition to the name of the column where I put date_restitution, there are real dates.

Answer 1

You can use UNION:

 select English, French from Table1 UNION ALL select English, French from Table2 

or full outer join

 select distinct coalesce(T1.English, T2.English), coalesce(T1.French, T2.French) from Table1 T1 full outer join Table2 T2 on T1.English = T2.English 

EDIT:

Assuming you want restitutions.date_restitution to appear instead of date_location for restitution records -

 SELECT l.num_client, l.date_location, l.duree, to_number(null) km_restitution, to_number(null) km_parcouru FROM locations l UNION ALL SELECT r.num_client, r.date_restitution date_location, 0 duree, r.km_restitution, r.km_parcouru FROM restitutions r 

FURTHER IMAGE (based on the results provided):

 select l.id_agence, l.num_immatricul, l.num_client, l.duree, l.date_location, decode(r.date_restitution, NULL,'----------------', 'date_restitution') as date_restitution -- or just r.date_restitution from location l left outer join restitution r on l.id_agence = r.id_agence and l.num_immatricul = r.num_immatricul and l.num_client = r.num_client and l.date_location <= r.date_restitution 

Answer 2

You really need a union:

 SELECT English, French FROM T1 UNION SELECT English, French FROM T2 

If you don't need duplicates, you can use UNION ALL

Edit after OP comment:

 SELECT l.num_client, l.id_agence, l.num_immatricul FROM locations l UNION SELECT r.num_client, r.id_agence, r.num_immatriculation FROM restitutions r 

Answer 3

The following should do it.

 SELECT tab1.English, tab1.French UNION SELECT tab2.English, tab2.French 

Answer 4

For other readers who may have the same issue. From the experience that I had with this problem, it would be nice to join the table places and restitutions, since both of them have almost the same attributes and data. Finally, I decided to modify my database and create a new table containing both location attributes and restitution, and set some values ​​that were not accessible to NULL . This will reduce the number of joins between tables and queries, it will be easier to handle.