Urgent , How to write this SQL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Urgent , How to write this SQL

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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 )

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    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

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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 ,







  4. #4
    Join Date
    Jan 2001
    Posts
    642
    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

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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]


  6. #6
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Maybe it's mission impossible . It seems a very simple question , But Oracle SQL can not achive :-) .

    Hope Xpert can solve it out


  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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 .



  9. #9
    Join Date
    Aug 2000
    Posts
    462
    Could you use a cursors in PL/SQL? Then you could order the results and put them together if that's appropriate.

  10. #10
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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
  •  


Click Here to Expand Forum to Full Width