
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 )

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

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 ,

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

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]

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

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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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 .

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

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
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
