sql query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: sql query

  1. #1
    Join Date
    Apr 2002
    Posts
    34

    sql query

    Hi

    I have 2 tables country and state that has the following data.

    country
    =======

    code value
    ---------------
    in india
    us usa
    uk britain

    state

    code value
    ----------------
    ca california
    mi michigan
    io iowa

    i am getting the value for code (tempcode) from a different source. If tempcode is one among the codes that is in state table then i want to retrive the country.code as 'US' else i want to retrieve the value as it is. there is no link between the 2 tables.

    Please help to provide with a sql query on this. Can we use decode function in this case.

    Please reply as this is urgent.
    VVS Raju
    8i DBA OCP

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Without joining tables how can u get desired results..

    the ans wud be no to ur question..


    Try with referencing code column of country as country_code in state table.

    during loading from tempcode(source)..build a logic to load appropriate country code.

    Abhay.
    Last edited by abhaysk; 04-14-2003 at 03:30 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    May 2002
    Posts
    108
    SELECT 'US' FROM state WHERE state.code = temp.code
    UNION ALL
    SELECT country.code FROM country
    WHERE NOT EXISTS(SELECT 'US' FROM state WHERE state.code = temp.code)
    AND country.code = temp.code

    HTH
    - Nandu
    Never give up !

    Nanda Kumar - Vellore

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by nandu
    SELECT 'US' FROM state WHERE state.code = temp.code
    UNION ALL
    SELECT country.code FROM country
    WHERE NOT EXISTS(SELECT 'US' FROM state WHERE state.code = temp.code)
    AND country.code = temp.code

    HTH
    - Nandu
    error it will not give required o/p

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    May 2002
    Posts
    108
    error it will not give required o/p

    Abhay, Why? !!

    I guess one of us have got the requirement wrong !!!

    - Nandu
    Never give up !

    Nanda Kumar - Vellore

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by nandu

    Abhay, Why? !!

    I guess one of us have got the requirement wrong !!!

    - Nandu
    Check his quote

    If tempcode is one among the codes that is in state table then i want to retrive the country.code as 'US'
    Code:
    SELECT 'US' FROM state WHERE state.code = temp.code
    correct.

    But
    else i want to retrieve the value as it is. there is no link between the 2 tables.
    Code:
    SELECT country.code FROM country 
    WHERE NOT EXISTS(SELECT 'US' FROM state WHERE state.code = temp.code)
    AND country.code = temp.code
    error. ( Temp.code consists codes among state.code or not )

    If not he wants

    in india
    uk britain

    But from ur code the join "country.code = temp.code" fails watsoever.

    coz it has codes like (CA,IO and so). If not he want its particular country, which you cant get.

    Just an eg
    Say temp.code='BN' ...its code of bangalore city ( country -> India )

    from ur code nither prior union will match not after union.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    May 2002
    Posts
    108
    You have made few points Abhay. True.

    But reading between the lines.

    If tempcode is one among the codes that is in state table then i want to retrive the country.code as 'US'
    He doesn't say that tempcode will not contain the values of country code. He has just mentioned that
    there is no link between the 2 tables.
    I believe it is one of those applications(as we see on the internet)to select the state if you are residing in US etc., etc,.

    I believe he wants to select the Country as US if the value in Tempcode finds a match in State table or the equivalent of the country code which the Tempcode will carry.

    You have assumed the design to be in fine granularity
    ie., Bangalore, Chennai.. Melbourne, Manchester etc.,

    In that case he would not have post this question at all

    Would you agree?

    - Nandu
    Never give up !

    Nanda Kumar - Vellore

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    All i can say is, let him come back with requirement precisely.

    Abahy.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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