Printable View
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.
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
This query appears to either require a position and/or an order to meet your requirements. I suggest looking at adding a new column to track order or use the pseudo-column rownum to handle location.
select 'a', 'b', 'c1', 'd1'
from (select 'a', 'b', 'c1', rownum from t1) a, (select d1, rownum from t2) b
where a.rownum = b.rownum(+) OR a.rownum = b.rownum-1 OR a.rownum = b.rownum+1
I hope this helps.
[Edited by Zaggy on 06-01-2001 at 12:39 AM]
Hi , Zaggy :
The solution is good . In fact , I have created the view to
hand the rownum column . But the problem is , The combination of A and B are multiple in T1 (T2) , So rownum
will show the sequence no of this record in whole table . So still can not achivement the desired result .
Any other solution ?
Any further instruction ?
This is the query you want, I believe. If it were possible to have more records in T2 than T1 at any time, you would need to do a UNION of the same statement with the outer-joins flipped - make sense? This statement will only work for T1 set > T2 set.
SELECT
---T1.Col1,
---T1.Col2,
---T1.Col3,
---T2.Col3
------AS Col4
FROM
---(
------SELECT
---------T1.*,
---------ROW_NUMBER()---
------------OVER---(
---------------PARTITION BY
------------------Col1,
------------------Col2
---------------ORDER BY
------------------Col3
---------------)
------------AS RN
------FROM
---------T1
---)---T1,
---(
------SELECT
---------T2.*,
---------ROW_NUMBER()---
------------OVER---(
---------------PARTITION BY
------------------Col1,
------------------Col2
---------------ORDER BY
------------------Col3
---------------)
------------AS RN
------FROM
---------T2
---)---T2
WHERE
---T2.Col1---(+)=---T1.Col1---AND
---T2.Col2---(+)=---T1.Col2---AND
---T2.RN------(+)=---T1.RN
(as always - replace --- with a 3-char tab for things to line up)
Now, the only trick with Analytical functions is that PL/SQL can't handle them. If this statement is to be run from a PL/SQL block, then the statement will need to be changed into a dynamic SQL statement - make sense? It's not my fault Oracle is stupid - they are fixing this oversight in 9i ;)
Let me know if you this doesn't work
- Chris
Hi , Friend ,
Your query is absolute right ( I have not run it , but from the
logic , I feel it's correct ) . The difficulty is make the PL/SQL function . I will study on it .
Anyway , thanks a lot for your help .
best regards
ligang