Results 1 to 2 of 2

Thread: Self Joins

  1. #1
    Join Date
    Oct 2003
    Posts
    26

    Question Unanswered: Self Joins

    Hi, I want to display the results of the following query in my
    program.


    select a.cdecode as cdecode, a.cdename as cdename, a.cdecallid as
    cdecallid, a.cderecID as cderecid, b.cdecode as bcdecode, b.cdename as
    bcdename, b.cdecallid as bcdecallid, b.cderecid as bcderecid
    from shrcodemaster a
    right outer join shrcodemaster b
    on a.cdeRecID = b.cdeRecid
    and a.cdecallid = 'B'


    The query is giving me the following results

    cdecode cdename cdecallid cderecid bcdecode bcdename bdecallid
    bcderecid
    MYR Malaysian ringgit B 2 MYR Malaysian ringgit B
    2
    NULL NULL NULL NULL AUD Australian dollar S 3
    USD US dollar B 4 USD US dollar B 4
    CNY Chinese yuan B 5 CNY Chinese yuan B
    5
    NULL NULL NULL NULL JPY Japanese yen (1) S 6
    IDR Indonesian Rupiah B 7 IDR Indonesian Rupiah B 7
    TWD New Taiwan dollar B 8 TWD New Taiwan dollar B 8
    HKD Hong Kong dollar B 9 HKD Hong Kong dollar B 9
    NULL NULL NULL NULL sgl Singapore S 10
    NULL NULL NULL NULL MY Malaysia S 11
    CN China B 12 CN China B 12

    How can I change my query so that for a cderecid, if cdecallid is 'B'
    then it should return NULL values for bcdecode, bcdename, bcdecallid,
    bcderecid.

    Just like for a bcdrecid, if bcdecallid is S, it is returning NULL
    values for cdecode,cdename,cdecallid, cderecid.

    Pls assist me. Its quite urgent.

    I want out put similar to

    cdecode cdename cdecallid cderecid bcdecode bcdename bdecallid
    bcderecid
    MYR Malaysian ringgit B 2 NULL NULL NULL
    NULL
    NULL NULL NULL NULL AUD Australian dollar S 3
    USD US dollar B 4 NULL NULL NULL
    NULL
    CNY Chinese yuan B 5 NULL NULL NULL
    NULL
    NULL NULL NULL NULL JPY Japanese yen (1) S 6
    IDR Indonesian Rupiah B 7 NULL NULL NULL
    NULL
    TWD New Taiwan dollar B 8 NULL NULL NULL
    NULL
    HKD Hong Kong dollar B 9 NULL NULL NULL
    NULL
    NULL NULL NULL NULL sgl Singapore S 10
    NULL NULL NULL NULL MY Malaysia S 11
    CN China B 12 NULL NULL NULL NULL

    Regards,
    Omavlana

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you want to use a left outer join instead of a right outer join, or perhaps a full outer join if you want to show missing records from both sides of the join. But then again, both sides are the same table. And both instances are joined on the same field. I have to tell you, I'm going freaking cross-eyed looking at your code.

    a.cdecode as cdecode?
    b.cderecid as bcderecid?
    a.cdecallid = 'B' ?

    I hope you haven't named your database something like "abc_db"!

    Do your future self (as in six months from now) a huge favor and use more descriptive aliases. Using aliases like "a" and "b" may save you a few seconds of typeing time now, but it may cost you hours of debugging time later.

    blindman

Posting Permissions

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