Urgent , How to write this SQL

[ligang]

I have 2 tables :

T1 : PK is A,B,C ;
T2 : PK is A,B,D;

Data is looks like this

T1: A , B , C1 T2: A , B , D1
A , B , C2 A , B , D2
A , B , C3

(For Same A , B combination , The records number in T1 is always large than in T2 )

I want to write a sql to show such result:

A , B , C1 , D1 ,
A , B , C2 , D2 ,
A , B , C3 , blank


How do I do ? ( the question is urgent , Please help me )

[badrinathn]

Table A) a b c1
a b c2
a b c3

Table B) a b d1
a b d2
a b d3

the expected out put is

a b c1 d1
a b c1 d2
a b c1 d3
a b c2 d1
a b c2 d2
a b c2 d3
...
..
As these to tables are independent , you may not be able to get your desired result.

If your tables looked like this,
col1 col2
-------------------------------
Table A) a1 c1
a2 c2
a3 c3
a4 c4



Table B) a1 d1
a2 d2
a3 d3


select a.c1,a.c2,a.c3,b.c4
from tab A a, tab B b
where a.col1 = b.col1(+)



Badrinath

[ligang]

Hi , Friend ,
It's not my expectation
a b c1 d1
a b c1 d2
a b c2 d1
a b c2 d2
a b c3 d1
a b c3 d2

it should be
a b c1 d1
a b c2 d2
a b c3 [blank]

The record number should be 3 which is same as T1 ,
not 3*2 ,

[badrinathn]

I am not sure, but you could try this

select a.col1, a.col2,a.col3,b.col4
from tabA a, tabB b
where a.col1+a.col2+a.col3 = b.col1+b.col2+b.col3(+)




i mean, combine the 3 columns and use the outer join

Let me know the result

badrinath

[ligang]

Sorry , It's not working .
It will show 3*2 records not 3 records .



Wrong result :

a b c1 d1
a b c1 d2
a b c2 d1
a b c2 d2
a b c3 d1
a b c3 d2

It should be :

a b c1 d1
a b c2 d2
a b c3 [blank]

[ligang]

Maybe it's mission impossible . It seems a very simple question , But Oracle SQL can not achive :-) .

Hope Xpert can solve it out

[jmodic]

It is not possible because you either missed to tell us some important information or your design is inadequate.

In table T1 you have columns: A, B, C
In table T2 you have columns : A, B, D

Lets say you have the following values in your tables:
T1:
'a', 'b', 'c1'
'a', 'b', 'c2'
'a', 'b', 'c3'

T2:
'a', 'b', 'd1'
'a', 'b', 'd2'

You want the following output from tables T1 and T2:

'a', 'b', 'c1', 'd1'
'a', 'b', 'c2', 'd2'
'a', 'b', 'c3', NULL

Obviously your join is based on: T1.A=T2.A AND T1.B=T2.B

But what about the relation between columns T1.C and T2.D? What condition should force values 'c1' and 'd2' to be in the same output record? Why not 'c1' and 'd2'? Or 'c3' and 'd1'? Or 'c3' and 'd2', Or....

Something is missing.

[ligang]

Hi , Modic ,
Your understanding is absolutely right . There are no relationship between column c in T1 and column d in T2 ,
can be any sequence . But that's the user requirement :-)

The output can be

'a', 'b', 'c1', 'd1'
'a', 'b', 'c2', 'd2'
'a', 'b', 'c3', NULL

or

'a', 'b', 'c1', 'd2'
'a', 'b', 'c2', 'd1'
'a', 'b', 'c3', NULL

both are correct .

I feel it's not possible in SQL :-)

Please help .

[kmesser]

Could you use a cursors in PL/SQL? Then you could order the results and put them together if that's appropriate.

[ligang]

Hi , Kmesser ,
Your idea is good , But it's the last alternative . If possible , I still want to make it done just by SQL . I don't want to write PL/SQL basically .

Please advise

ligang