1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL query doubt

Discussion in 'SQL PL/SQL' started by krithika@2001, Aug 20, 2009.

  1. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    I have a two tables called TPL and TPL10.TPL has a column called CO_CODE and TPL10 has NS_CO_CODE.I am doing a union on both the columns

    select distinct CO_CODE from TPL
    union
    select distinct NS_CO_CODE from TPL10,TPL where TPL10.ano=TPL.ano

    The count is 60.

    I need to select RECIP_ID for these 60 records

    which is in the table TPL.

    SO
    when i write

    select distinct CO_CODE,RECIP_ID from TPL
    union
    select distinct NS_CO_CODE,RECIP_ID from TPL10,TPL where TPL10.ano=TPL.ano


    i am getting 115 records.becoz i am getting duplicate CO_CODES

    So can you please let me know how to use queries in such a way i should select distinct CO_CODES as well as retrieve RECIP_ID for those co_codes.

    Thanks and Regards
    Krithika Swaminathan
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Is this what you are looking for ?


    Code (SQL):
    SELECT DISTINCT CO_CODE,RECIP_ID
    FROM TPL T
    WHERE EXISTS ( SELECT NULL
                        FROM TPL10 T10
                        WHERE T10.ano=T.ano
                        AND  T.CO_CODE = T10.NS_CO_CODE )
     
     
  3. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Hi Raj
    thanks for your reply

    But my requirement is slightly different

    I will give you the full query

    select distinct CO_CODE from TPL
    where CO_CODE not in
    (select distinct NS_CO_CODE from TPL1)
    union
    select distinct CO_CODE from TPL10,TPL where TPL.ano=TPL10.ano and CO_CODE not in
    (select distinct ND_CO_CODE from TPL2)

    In this case I get 60 distinct CO_CODES

    but if i include RECIP_ID also in the select statement
    such as

    select distinct CO_CODE,RECIP_ID from TPL
    where CO_CODE not in
    (select distinct NS_CO_CODE from TPL1)
    union
    select distinct CO_CODE,RECIP_ID from TPL10,TPL where TPL.ano=TPL10.ano and CO_CODE not in
    (select distinct ND_CO_CODE from TPL2)

    I am getting 115 records.But I need only distinct CO_CODES

    Please let me know how to do

    Thanks and Regards
    Krithika Swaminathan