Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    15

    Unanswered: A table in 2 Outer joins

    Can a table participate in both outer joins - one as inner member and other as outer member?

    For eg: Table schema:
    Table A (a1)
    Table B (a1, b1)
    Table C (b1, c1)

    Data :
    A: 1, 2, 3
    B: (1, 4), (1,5)
    C: (4,6)

    The result should be
    1 4 6
    2 5 null
    3 null null

    The following (sample) queries do not give reqd results:
    select a1, b1, c1
    from A, B, C
    where A.a1 *= B.a1
    and B.b1 *= C.b1

    select a1, b1, c1
    from A, B, C
    where A.a1 *= B.a1
    and B.b1 =* C.b1

  2. #2
    Join Date
    Dec 2002
    Posts
    104

    Re: A table in 2 Outer joins

    Originally posted by sankaran_arumug
    Can a table participate in both outer joins - one as inner member and other as outer member?

    For eg: Table schema:
    Table A (a1)
    Table B (a1, b1)
    Table C (b1, c1)

    Data :
    A: 1, 2, 3
    B: (1, 4), (1,5)
    C: (4,6)

    The result should be
    1 4 6
    2 5 null
    3 null null

    The following (sample) queries do not give reqd results:
    select a1, b1, c1
    from A, B, C
    where A.a1 *= B.a1
    and B.b1 *= C.b1

    select a1, b1, c1
    from A, B, C
    where A.a1 *= B.a1
    and B.b1 =* C.b1
    hi,

    I am lil confused here
    u want result set as
    1 4 6
    2 5 null /*here is the confusion as in table B col a1 just contains 1 in both records so how can u get a1 as 2 and b1 as 5 dont u think it shud be 1 5 null*/
    3 null null

    --Pooja

  3. #3
    Join Date
    Feb 2003
    Posts
    15
    I am sorry for that.

    You are right, the result should be
    1 4 6
    1 5 null
    3 null null

  4. #4
    Join Date
    Dec 2002
    Posts
    104
    Originally posted by sankaran_arumug
    I am sorry for that.

    You are right, the result should be
    1 4 6
    1 5 null
    3 null null
    hello,

    Can u please confirm this is the result set u want , as looking at this result set all the values from table A are not present.

    If u require all the values from table A and corresponding values from B and C then in that case,result set shud something be like this.

    1 4 6
    1 5 null
    2 null null
    3 null null

    --Pooja

  5. #5
    Join Date
    Dec 2003
    Location
    Hyderabad,India
    Posts
    2

    Re: A table in 2 Outer joins

    I could not test this, as I don't have access to the system right now, can you try this out?

    select A.a1, B.b1, C.c1
    from A,B,C
    where A.a1 =* B.a1 and B.b1 =* C.b1


    I hope this will work
    -----------------------------


    Originally posted by sankaran_arumug
    Can a table participate in both outer joins - one as inner member and other as outer member?

    For eg: Table schema:
    Table A (a1)
    Table B (a1, b1)
    Table C (b1, c1)

    Data :
    A: 1, 2, 3
    B: (1, 4), (1,5)
    C: (4,6)

    The result should be
    1 4 6
    2 5 null
    3 null null

    The following (sample) queries do not give reqd results:
    select a1, b1, c1
    from A, B, C
    where A.a1 *= B.a1
    and B.b1 *= C.b1

    select a1, b1, c1
    from A, B, C
    where A.a1 *= B.a1
    and B.b1 =* C.b1

  6. #6
    Join Date
    Dec 2003
    Location
    Hyderabad,India
    Posts
    2

    Re: A table in 2 Outer joins

    I could not test this, as I don't have access to the system right now, can you try this out?

    select A.a1, B.b1, C.c1
    from A,B,C
    where A.a1 =* B.a1 and B.b1 =* C.b1


    I hope this will work
    -----------------------------


    Originally posted by sankaran_arumug
    Can a table participate in both outer joins - one as inner member and other as outer member?

    For eg: Table schema:
    Table A (a1)
    Table B (a1, b1)
    Table C (b1, c1)

    Data :
    A: 1, 2, 3
    B: (1, 4), (1,5)
    C: (4,6)

    The result should be
    1 4 6
    2 5 null
    3 null null

    The following (sample) queries do not give reqd results:
    select a1, b1, c1
    from A, B, C
    where A.a1 *= B.a1
    and B.b1 *= C.b1

    select a1, b1, c1
    from A, B, C
    where A.a1 *= B.a1
    and B.b1 =* C.b1

  7. #7
    Join Date
    Dec 2002
    Posts
    104

    Re: A table in 2 Outer joins

    Originally posted by rambabub
    I could not test this, as I don't have access to the system right now, can you try this out?

    select A.a1, B.b1, C.c1
    from A,B,C
    where A.a1 =* B.a1 and B.b1 =* C.b1


    I hope this will work
    -----------------------------
    hi!

    accordint to this query C is the outermost table so, it will pick all values from C , i.e, 6 and matching values from B,i.e,4 and then matching values from A , i.e,1 (Correct me if i am wrong)

    so the resultset is
    1,4,6

    --Pooja

  8. #8
    Join Date
    Feb 2003
    Posts
    15
    Originally posted by pooja
    hello,

    Can u please confirm this is the result set u want , as looking at this result set all the values from table A are not present.

    If u require all the values from table A and corresponding values from B and C then in that case,result set shud something be like this.

    1 4 6
    1 5 null
    2 null null
    3 null null

    --Pooja
    Yes indeed, the one mentioned by you is what I need

  9. #9
    Join Date
    Dec 2002
    Posts
    104
    Originally posted by sankaran_arumug
    Yes indeed, the one mentioned by you is what I need
    If u need resultset as

    1 4 6
    1 5 NULL
    2 NULL NULL
    3 NULL NULL

    then query is

    select A.a1,B.b1,C.c1
    from A,B,C
    where A.a1 *= B.a1
    and B.b1 *=C.b1

    Hope this will work for u,
    --Pooja

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •