Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hi everybody, Say, I have two ta

Status
Not open for further replies.

SashaBuilder3

Programmer
Jan 13, 2002
131
CA
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
 
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.
 
jimbopalmer,

Thanks a lot!

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


Alexandre
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top