Country Fallback - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Country Fallback

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-11-02 : 16:19:03

Ok, I'm just messing with an idea here but I'd like to offer a localised country drop down list to our users. I've got the ISO codes for EN and FR to test with. My idea is to have two tables:


CREATE TABLE [dbo].[tbl_Countries](
	[Alpha2Char] [char](2) NOT NULL,
	[Alpha3Char] [char](3) NOT NULL,
	[Number] [int] NOT NULL,
	[Country] [nvarchar](255) NOT NULL,
	[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_tbl_Countries_IsDeleted]  DEFAULT (0),
 CONSTRAINT [PK_tbl_Countries] PRIMARY KEY CLUSTERED 
(
	[Alpha2Char] ASC
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl_Countries_Localised](
	[Alpha2Char] [char](2) NOT NULL,
	[LocalName] [nvarchar](500) NOT NULL,
	[Language] [varchar](5) NOT NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tbl_Countries_Localised]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Countries_Localised_tbl_Countries] FOREIGN KEY([Alpha2Char])
REFERENCES [dbo].[tbl_Countries] ([Alpha2Char])
GO
ALTER TABLE [dbo].[tbl_Countries_Localised] CHECK CONSTRAINT [FK_tbl_Countries_Localised_tbl_Countries]

The first is a fully populated master list of all the ISO codes and their English name (the one that'll primarily be listed). The second table is the localised version i.e. "Afrique Du Sud" instead of "South Africa".

Now, I've got this working with the following UNION but it uses a "NOT IN" and I'm not sure that's the best way, could someone confirm whether that's ok or whether there's a better way?


DECLARE @lang varchar(5)
SET @lang = 'fr-FR'

SELECT
	Alpha2Char, 
	Alpha3Char,	
	Number, 
	Country
FROM
	dbo.tbl_Countries
WHERE
	IsDeleted = '0'
GROUP BY
	Alpha2Char, 
	Alpha3Char,	
	Number, 
	Country
HAVING
	Alpha2Char NOT IN (
		SELECT
			Alpha2Char
		FROM
			dbo.tbl_Countries_Localised
		WHERE
			Language = @lang
	)

UNION ALL

SELECT
	Alpha2Char, 
	Alpha3Char,	
	Number, 
	LocalName As Country
FROM
	dbo.Countries
WHERE
	IsDeleted = '0'
  AND
	Language = @Lang

GROUP BY
	Alpha2Char, 
	Alpha3Char,	
	Number, 
	LocalName

ORDER BY
	Country

TIA

Tim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-02 : 17:28:20

use LEFT JOIN with COALESCE


select  c.Alpha2Char, 
        c.Alpha3Char, 
        c.Number, 
        Country = coalesce(l.LocalName, c.Country)
from    tbl_Countries c left join tbl_Countries_Localised l
        on  c.Alpha2Char = l.Alpha2Char
        and c.language   = l.language
where   c.IsDeleted = '0'
and	c.Language = @Lang

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-11-03 : 03:10:04

Hi KH,

Thanks but there's no language in the default table to join on, the reasoning behind that is I know the language is en. Using LEFT JOIN with COALESCE means that if a localised record is ommited from the localised table it doesn't fall back, here's some example data:


INSERT INTO dbo.tbl_Countries (Alpha2Char, Alpha3Char, Number, Country)
SELECT 'ZA', 'ZAF', 710, 'South Africa' UNION ALL
SELECT '__', '___', 0, '_Test'

NSERT INTO dbo.tbl_Countries_Localised (Alpha2Char, LocalName, Language)
SELECT 'ZA', 'Afrique Du Sud', 'fr-FR'

Now if you compare the two queries you'll see that your's drops _Test as there's no localised version...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-03 : 03:38:18

[code]select c.Alpha2Char,
c.Alpha3Char,
c.Number,
coalesce(l.LocalName, c.Country) Country
from tbl_Countries c
left join tbl_Countries_Localised l on c.Alpha2Char = l.Alpha2Char and c.language = @lang
where c.IsDeleted = 0[/code]

Peter Larsson
Helsingborg, Sweden

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-11-03 : 03:58:29

ahhh I had a feeling it was something to do with a calculated join, thanks Peter.

Tim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-03 : 05:59:21

It was khtan who lead you into the right path...

Peter Larsson
Helsingborg, Sweden

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-11-03 : 06:14:45

:) I don't deny it! Thanks to you both

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 14:51:17

You really should include a PK constraint on tbl_Countries_Localised(Alpha2Char, Language).
You might consider a check constraint on Language so that someone can't fat-finger 'frFR when they mean 'fr-FR'. The other option would be to have a language table with a FK on tbl_Countries_Localised.Language.

Jay White

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-11-03 : 15:17:52

Thanks Jay, there is a composite PK, I just didn't paste it there, the tables will be manually authored by us anyways but the PK is the Language and Alpha2Char to ensure someone can't add two records for the same item, as for the languages option, that's pulled from .Net's Globalisation namespace and atm I'm thinking I'll restrict it to the Language i.e. FR not language/country (fr-FR).

Thanks for the pointers though :)

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources