Hi everybody, Say, I have two ta

[SashaBuilder3]

Hi everybody,

Say, I have two tables t1 and t2:

Table t1:

f1 f2
---------
a 7
c 3
d 6
e 7


Table t2:

f1 f3
---------
a 5
b 2
d 1
f 3


I need to join the tables in a way to produce the following result table (matching rows by f1 field):

f1 f2 f3
------------
a 7 5
b . 2
c 3 .
d 6 1
e 7 .
f . 3

(dots represent NULLs)

Can anyone help me in writing a proper SQL statement?


Thanks,

Alexandre

 

[jimbopalmer]

select t1.f1, f2, f3 from t1, t2 where t1.f1 = t2.f1 (+)
union
select t1.f1, f2, f3 from t1, t2 where t1.f1 (+) = t2.f1; I tried to remain child-like, all I acheived was childish.

 

[SashaBuilder3]

jimbopalmer,

Thanks a lot!

Can you tell me what those pluses (+) mean?


Alexandre

 

[jimbopalmer]

if we just say t1.f1 = t2.f1 then it will only show us the rows that are in both tables, no nulls.

If we say t1.f1 = t2.f1 (+) then it is permissable for t2 to not match any row in t1, the value f3 can be null.

If we say t1.f1 (+)= t2.f1 then it is permissable for t1 to not match any row in t2, the value f2 can be null.

the union of each adds the two lists and eliminates duplicates that existed once in each list. I tried to remain child-like, all I acheived was childish.

 

[SashaBuilder3]

jimbopalmer,

Thanks again!

Alexandre