Joining two tables in Oracle sql

Question

I have 2 tables. One of the tables have 7 values and the other table has 5 values. These tables have their primary keys in common. 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 another one:

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 the values four and five. How can I go about doing 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      ---------------

Apart from the column name, where i put date_restitution contains real dates.

Answer 1

For other readers who might have the same problem. From the experience I had with this problem, it would be a good idea to join tables locations and restitutions since both of them have almost the same attributes and data. I finally decided in changing my database and creating a new table that contains both the attributes of location and restitution and setting some not availabe values to NULL. This would reduce a lot of joins between tables and queries would be easier to handle.

Answer 2

You could use a UNION:

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

or a 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 in place 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 EDIT (based on supplied results):

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 3

You actually need a union:

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

If you don't care about duplicates, you can use UNION ALL

Edit after OP's 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 4

The following should do it.

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