CODEWITHSUNDEEP
sqlsql-server
Selrac
Selrac

Reputation: 2293

SQL filter countries by numbered region and also globally

I'm trying to build an SQL query using Microsoft SQL server, where countries can be filtered by regions numbered from 0 to 4, 0 meaning Global

I have constructed a query which filters the regions 1 to 4 correctly as per example below, but I can not figure out how to get all the countries if 0 (being Global) is selected as a region.

declare @region as integer
set @region = 1
select *
 from Country where 
 case LEFT(name,2) 
when 'US' THEN 1
when 'UK' THEN 2
WHEN 'DE' THEN 3
WHEN 'CZ' THEN 4
WHEN 'ES' THEN 4
WHEN 'FR' THEN 4
WHEN 'IT' THEN 4
WHEN 'NL' THEN 4
WHEN 'PL' THEN 4
WHEN 'RO' THEN 4
WHEN 'SE' THEN 4
END = @region

Any ideas? Is there better ways to construct the query?

Thanks

Upvotes: 0

Views: 1047

Answers (3)

Nitu Bansal
Nitu Bansal

Reputation: 3856

you can put a field named region in country table then put this condition like below

   select * from Country where @region = 0 or Country.region =@region

you can do in your code like this

  select * from Country where @region = 0 OR (case LEFT(name,2) 
  when 'US' THEN 1
  when 'UK' THEN 2
  WHEN 'DE' THEN 3
  WHEN 'CZ' THEN 4
  WHEN 'ES' THEN 4
  WHEN 'FR' THEN 4
  WHEN 'IT' THEN 4
  WHEN 'NL' THEN 4
  WHEN 'PL' THEN 4
  WHEN 'RO' THEN 4
  WHEN 'SE' THEN 4
  END = @region)

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

select *
from Country 
where @region = 0 OR
(case LEFT(name,2) 
when 'US' THEN 1
when 'UK' THEN 2
WHEN 'DE' THEN 3
WHEN 'CZ' THEN 4
WHEN 'ES' THEN 4
WHEN 'FR' THEN 4
WHEN 'IT' THEN 4
WHEN 'NL' THEN 4
WHEN 'PL' THEN 4
WHEN 'RO' THEN 4
WHEN 'SE' THEN 4
END = @region)

Is the way to make it work. However, instead of having this hard coded case statement, you should have a look up table that holds country names and the region code that is in. Then you can rewrite the SQL statement to pull the country code from that table and compare it to region.

Upvotes: 0

Szymon
Szymon

Reputation: 43023

To return all countries when @region is 0:

declare @region as integer
set @region = 1
select *
 from Country where 
    @region = 0
 OR
     case LEFT(name,2) 
        when 'US' THEN 1
        when 'UK' THEN 2
        WHEN 'DE' THEN 3
        WHEN 'CZ' THEN 4
        WHEN 'ES' THEN 4
        WHEN 'FR' THEN 4
        WHEN 'IT' THEN 4
        WHEN 'NL' THEN 4
        WHEN 'PL' THEN 4
        WHEN 'RO' THEN 4
        WHEN 'SE' THEN 4
    END = @region

Also, can you add region as a column to Country table? That would make the whole thing easier?

Upvotes: 2

Related Questions