Re: Two SQL questions.

From: Martin Haltmayer <Martin_Haltmayer_at_KirchGruppe.de>
Date: 1997/09/20
Message-ID: <3423B6B4.7797F09B@KirchGruppe.de>#1/1

stephen wrote:
>
> Hello all,
>
> I have two questions about SQL statements:
>
> 1)How to know the version of SQL*NET in SQL*PLUS? (i.e. by which
> SQL statement)
>
> 2)Assume three tables in Oracle.
>
> Table Attributes
> ----- ----------
> T1 A, B
> T2 A, C
> T3 A, D
>
> A B A C A D
> ----- ---- -----
> a1 b1 a1 c1 a1 d1
> a2 b2 a1 c2
> a3 b3 a1 d3

I presume this should be

A C

---

a1 c1
a2 c2

and

A D

---

a1 d1

a3 d3

>
> I want the result is:
>
> row1: a1 b1 c1 d1
> row2: a2 b2 c2
> row3: a3 b3 d3
>
> How to do this by a single SQL statement?
>
> Thanks,
> Stephen

I tried the following:

create table t1 (a varchar2 (2), b varchar2 (2));
create table t2 (a varchar2 (2), c varchar2 (2));
create table t3 (a varchar2 (2), d varchar2 (2));
 
insert into t1 values ('a1', 'b1');
insert into t1 values ('a2', 'b2');
insert into t1 values ('a3', 'b3');
insert into t2 values ('a1', 'c1');
insert into t2 values ('a2', 'c2');

insert into t3 values ('a1', 'd1');
insert into t3 values ('a3', 'd3');  

select t1.a, b, c, d
from t1, t2, t3
where t1.a = t2.a (+)
and t1.a = t3.a (+);  

drop table t1;
drop table t2;
drop table t3;

I got

A B C D
-- -- -- --
a1 b1 c1 d1
a2 b2 c2
a3 b3 d3

I think that's you wanted.

-- 
Martin Haltmayer

Received on Sat Sep 20 1997 - 00:00:00 CDT