CODEWITHSUNDEEP
sqloracle-databaseunion
shankar
shankar

Reputation: 441

UNION and NOT EXISTS throws Error

I have a table which contains city name in both English and french name only if the name are different else it will have only English name. NOTE. Geocode will be same for the city CityA and CityA` since both are the same city but code will be different.

Table name= City

Columns are below 

city,code,language,geocode

Eg. 

cityA  Frc
cityA  Frc
cityB  Eng
CityC  Eng
CityD  Eng
CityD` FRC

So we can see from above example that CityA has both FRENCH and English name while CityB does not have because CityB has same spelling /accent both in french and english.

What I want to do is based on user language, if user language is french, then return all french name plus english name which do not have french accent. so from above it should return CityA`,CityB,CityC,CityD'

Similarly if user language is english return all english name. CityA,CityB,CityC.CityD.

Below is what i tried

select a.city,a.code from country a
where a.language=userenv('lang') -- it will be either french or english 
union
select b.city,b.code from country b
where b.language='Eng' 
AND not exists( select geocode from country 
            where geocode = a.geocode)

I get error as a.geocode not identified.

Upvotes: 1

Views: 4152

Answers (2)

jarlh
jarlh

Reputation: 44786

Do a self full outer join, one table instance for userenv('lang') and one for English:

select coalesce(c1.city, c2.city), coalesce(c1.code, c2.code)
from country c1
full outer join country c2
   on  c1.code = c2.code 
   and c1.language = userenv('lang')
   and c2.language = 'Eng'

Use COALESCE() to pick the userenv('lang') values if available, otherwise the English values.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You need for the table aliases to be correct.

I would recommend:

select c.city, c.code
from country c
where c.language = userenv('lang') -- it will be either french or english 
union all
select c2.city, c2.code
from country c2
where c2.language = 'Eng' and
      not exists (select 1
                  from country c3
                  where c3.geocode = c2.geocode
                 );

In your query, the alias for a is not known in the subquery. However, the table aliases should be abbreviations for the table name, rather than arbitrary letters.

That said, I think the query you really want is:

select c.city, c.code
from country c
where c.language = userenv('lang') -- it will be either french or english 
union all
select c2.city, c2.code
from country c2
where c2.language = 'Eng' and
      not exists (select 1
                  from country c3
                  where c3.geocode = c2.geocode and
                        c3.language = userenv('lang')
                 );

Upvotes: 2

Related Questions