Self Joins

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Omavlana
    #1

    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
    Tags: None
    • Shervin
      #2
      Re: Self Joins

      Oh man! It took me more than half an hour to parse your script and its
      output. Please write your codes more readable so people don't give up
      understanding your problem :-)
      Anyway, your desired result set didn't make sense to me, but I just assume
      what you have written is really what you need. So this is probably the query
      you want:

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

      I hope it work...

      Good luck,
      Shervin


      "Omavlana" <kiran@boardroo mlimited.com> wrote in message
      news:b14098ab.0 310021857.4de40 0f5@posting.goo gle.com...[color=blue]
      > 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[/color]


        Comment

        Previous template Next
        Working...