sql query

[raju_sbd]

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.

[abhaysk]

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.

[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

[abhaysk]

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.

[nandu]

error it will not give required o/p

Abhay, Why? !!

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

- Nandu

[abhaysk]

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'

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.

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.

[nandu]

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

[abhaysk]

All i can say is, let him come back with requirement precisely.

Abahy.