Re: Two SQL questions.

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 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?
>

I don't think you can. Joining the the other two tables to the first with an outer join on A will result in 4 rows of a1 and the a2, a3 rows comming back with nulls for C and D. A total of 6 rows in the result set.

If your example is the actual content, and not just a symbolic reference then you count try:

SELECT T1.A, T1.B, T2.C, T3.D FROM T1, T2, T3 WHERE substr(T1.A, 2, 1) = substr(T2.C, 2, 1) (+) AND substr(T1.A, 2, 1) = substr(T3.D, 2, 1) (+)

Would be better if the row number that is the actual relationship were in a seperate column.

Have a good time with your keyboard,
Gary Received on Thu Sep 18 1997 - 00:00:00 CEST