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.