JOINS and NULL for multilanguage database

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 2005 Forums

Transact-SQL (2005)

JOINS and NULL for multilanguage database

Author

Topic

Gudea
Starting Member

18 Posts

Posted - 2012-02-25 : 15:20:53

Hi.
I am programming a Stored to list Entities (Countries, and others) in a multilanguage scenario.

I have two tables for each translated entity plus Language list.

Language
Lang_Id int
Land_Name varchar
Lang_Defaut bit
Lang_Rquired bit

Country
Coun_Id int
Coun_ISO varchar
Coun_Flag varchar

CountryTr (Translation)
CoTr_Id int
CoTr_Coun_Id int
CoTr_Lang_Id int
CoTr_Name varchar (translated name for each Country)

I am trying to program a stored that lists all Countries with the name of the specified language (if available) otherwise the name in the default (mandatory) language. Both with the obtained language reference.

Its easy the first part

CREATE PROCEDURE dbo.CountryList
@Language_Id int
AS

SELECT
Coun_Id,
Coun_ISO,
CoTr_Name As Coun_Name,
Lang_Id
FROM
Country
JOIN
CountryTr
ON CoTr_Coun_Id = Coun_Id
JOIN
Language
ON Lang_Id = CoTr_Lang_Id
WHERE
Lang_Id = @Language_Id

(Don't know if it is running, I just re-wroted it here manually)

But I can't get the country if no translation is available for the specifyed languaje. Also I can't bring the default translation in that case.

For instance an expected result would be:
@Language_Id = 3 (spanish)

Id ISO Name Lang
1 US Estados Unidos 3
2 FR Francia 3
3 DE Germany 1 (no translation for Germany, English brought as default language)

Please, I need some hint here.

Thanks
Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 17:46:54

[code]
CREATE PROCEDURE dbo.CountryList
@Language_Id int
AS

SELECT
c.Coun_Id,
c.Coun_ISO,
ct.CoTr_Name As Coun_Name,
COALESCE(l1.Lang_Id,l2.Lang_Id) AS Lang_Id
FROM
Country c
LEFT JOIN CountryTr ct
ON ct.CoTr_Coun_Id = c.Coun_Id
LEFT JOIN Language l1
ON l1.Lang_Id = ct.CoTr_Lang_Id
CROSS JOIN Language l2
ON l2.Lang_Defaut = 1
WHERE
COALESCE(l1.Lang_Id,l2.Lang_Id) = @Language_Id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gudea
Starting Member

18 Posts

Posted - 2012-02-26 : 17:37:21

Wow thanks. CROSS JOIN...

Anyway it is giving me an syntax error near the word ON

ON l2.Lang_Defaut = 1 <----- in this line.

I checked twice for any typo...
If I comment this line, it works, but without the expected results.

Thanks in advance visakh

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-26 : 18:03:45

Because CROSS JOIN doesn't have an ON clause. It should be

CREATE PROCEDURE dbo.CountryList
@Language_Id int
AS

SELECT
c.Coun_Id,
c.Coun_ISO,
ct.CoTr_Name As Coun_Name,
COALESCE(l1.Lang_Id,l2.Lang_Id) AS Lang_Id
FROM
Country c
LEFT JOIN CountryTr ct
ON ct.CoTr_Coun_Id = c.Coun_Id
LEFT JOIN Language l1
ON l1.Lang_Id = ct.CoTr_Lang_Id
CROSS JOIN Language l2
WHERE
COALESCE(l1.Lang_Id,l2.Lang_Id) = @Language_Id

Cross join is explicitly a 'join' without matching columns, it all rows from one table to all from another.

--
Gail Shaw
SQL Server MVP

Gudea
Starting Member

18 Posts

Posted - 2012-02-26 : 18:59:44

Ok, now it compiles, but I am not getting the expected results

I can't see any reference to the default language now, so, I get:

1 US Estados Unidos 3
1 US Estados Unidos 3
1 US Estados Unidos 3
2 FR Francia 3
2 FR Francia 3
2 FR Francia 3

Three times each country but the one that doesn't have translation. a DISTINCT won't help here.

If I add another WHERE condition
AND l2.Lang_Defaut = 1

It shows once each country, but the one missing its translation.

1 US Estados Unidos 3
2 FR Francia 3

Missing:
3 DE Germany 1

Sorry, but I can't get any idea what else to try.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-26 : 19:11:14

Can you post sample data please for all involved tables and the expected results for that sample data

--
Gail Shaw
SQL Server MVP

Gudea
Starting Member

18 Posts

Posted - 2012-02-27 : 10:35:17

Here is a sample. I'll add logical deletion.

TABLE: Language
Lang_Id int
Land_Name varchar
Lang_Defaut bit
Lang_Rquired bit
Lang_Active bit

TABLE: Country
Coun_Id int
Coun_ISO varchar
Coun_Flag varchar
Coun_Active bit

TABLE: CountryTr (Translation)
CoTr_Id int
CoTr_Coun_Id int
CoTr_Lang_Id int
CoTr_Name varchar (translated name for each Country)
CoTr_Active bit

--------------------------------------------------------
DATA:

FOR Language:
Id Name Default Mandatory Active
1 'English' 1 1 1
2 'Spanish' 0 1 1
3 'French' 0 0 1

FOR Country:
Id ISO Flag Active
1 'US' 'us.gif' 1
2 'FR' 'fr.gif' 1
3 'DE' 'de.gif' 1

FOR CountryTr:
Id CId LId Name Active
1 1 1 'United States' 1
2 1 2 'Estados Unidos' 1
3 1 3 'Etats Units' 1
4 2 1 'France' 1
5 2 2 'Francia' 1
6 2 3 'France' 1
7 3 1 'Germany' 1
8 3 2 'Alemania' 1
9 3 3 'Allemagne' 0

As you can see the only non active translation is Germany in French.

So, if I call a list of Countries in English, everything would run fine.

CountryList @Lang_Id = 1
RESULT:
Id ISO Name LangId
1 'US' 'United States' 1
2 'FR' 'France' 1
3 'DE' 'Germany' 1

Also if I call for a spanish listing:

CountryList @Lang_Id = 2
RESULT:
Id ISO Name LangId
1 'US' 'Estados Unides' 2
2 'FR' 'Francia' 2
3 'DE' 'Alemania' 2

But if I list them in French, French translation for Germany will miss and default language translation would be brought (English)

CountryList @Lang_Id = 3
RESULT:
Id ISO Name LangId
1 'US' 'Etats Units' 3
2 'FR' 'France' 3
3 'DE' 'Germany' 1

I can't achieve this behavior for the listing Stored.

Thanks
Regards

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-28 : 04:34:05

Sorry, snowed under, don't know when I'll get a chance to look at it.

Why delete the table creation scripts? Now it's harder to run up a testable solution as I have to turn the descriptions into CREATE TABLE and the lists into INSERT statements.

--
Gail Shaw
SQL Server MVP

Gudea
Starting Member

18 Posts

Posted - 2012-02-28 : 07:10:15

Sorry I thought it was too pretentious you to create the whole database for me to test.
I'll post the script again if useful.

Thanks a lot

Gudea
Starting Member

18 Posts

Posted - 2012-02-28 : 12:25:22

I'll post a script to create the database just as I described here.

You will see that theese listings bring all countries since they have a translation for the specified language:

CountryList @Language_Id = 1 --English
CountryList @Language_Id = 2 --Spanish

But when listing in French, Germany is ommited cause it has no translation to that language.

CountryList @Language_Id = 3 --French

It is supossed that Germany would be listed also but with its default translation (English) as 'Germany'

Thanks a lot

/****** Object: ForeignKey [FK_CountryTr_Country] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CountryTr_Country]') AND parent_object_id = OBJECT_ID(N'[dbo].[CountryTr]'))
ALTER TABLE [dbo].[CountryTr] DROP CONSTRAINT [FK_CountryTr_Country]
GO
/****** Object: ForeignKey [FK_CountryTr_Language] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CountryTr_Language]') AND parent_object_id = OBJECT_ID(N'[dbo].[CountryTr]'))
ALTER TABLE [dbo].[CountryTr] DROP CONSTRAINT [FK_CountryTr_Language]
GO
/****** Object: Default [DF_Country_Coun_Active] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Country_Coun_Active]') AND parent_object_id = OBJECT_ID(N'[dbo].[Country]'))
Begin
ALTER TABLE [dbo].[Country] DROP CONSTRAINT [DF_Country_Coun_Active]

End
GO
/****** Object: Default [DF_CountryTr_CoTr_Active] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_CountryTr_CoTr_Active]') AND parent_object_id = OBJECT_ID(N'[dbo].[CountryTr]'))
Begin
ALTER TABLE [dbo].[CountryTr] DROP CONSTRAINT [DF_CountryTr_CoTr_Active]

End
GO
/****** Object: Default [DF_Language_Lang_Default] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Language_Lang_Default]') AND parent_object_id = OBJECT_ID(N'[dbo].[Language]'))
Begin
ALTER TABLE [dbo].[Language] DROP CONSTRAINT [DF_Language_Lang_Default]

End
GO
/****** Object: Default [DF_Language_Lang_Mandatory] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Language_Lang_Mandatory]') AND parent_object_id = OBJECT_ID(N'[dbo].[Language]'))
Begin
ALTER TABLE [dbo].[Language] DROP CONSTRAINT [DF_Language_Lang_Mandatory]

End
GO
/****** Object: Default [DF_Language_Lang_Active] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Language_Lang_Active]') AND parent_object_id = OBJECT_ID(N'[dbo].[Language]'))
Begin
ALTER TABLE [dbo].[Language] DROP CONSTRAINT [DF_Language_Lang_Active]

End
GO
/****** Object: StoredProcedure [dbo].[CountryList] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CountryList]
GO
/****** Object: Table [dbo].[CountryTr] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryTr]') AND type in (N'U'))
DROP TABLE [dbo].[CountryTr]
GO
/****** Object: Table [dbo].[Language] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Language]') AND type in (N'U'))
DROP TABLE [dbo].[Language]
GO
/****** Object: Table [dbo].[Country] Script Date: 02/28/2012 14:21:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND type in (N'U'))
DROP TABLE [dbo].[Country]
GO
/****** Object: Table [dbo].[Country] Script Date: 02/28/2012 14:21:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Country](
[Coun_Id] [int] NOT NULL,
[Coun_ISO] [varchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
[Coun_Flag] [varchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
[Coun_Active] [bit] NOT NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[Coun_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[Country] ([Coun_Id], [Coun_ISO], [Coun_Flag], [Coun_Active]) VALUES (1, N'US', N'us.gif', 1)
INSERT [dbo].[Country] ([Coun_Id], [Coun_ISO], [Coun_Flag], [Coun_Active]) VALUES (2, N'AR', N'ar.gif', 1)
INSERT [dbo].[Country] ([Coun_Id], [Coun_ISO], [Coun_Flag], [Coun_Active]) VALUES (3, N'DE', N'de.gif', 1)
/****** Object: Table [dbo].[Language] Script Date: 02/28/2012 14:21:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Language]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Language](
[Lang_Id] [int] NOT NULL,
[Lang_Name] [varchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
[Lang_Default] [bit] NOT NULL,
[Lang_Mandatory] [bit] NOT NULL,
[Lang_Active] [bit] NOT NULL,
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
[Lang_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[Language] ([Lang_Id], [Lang_Name], [Lang_Default], [Lang_Mandatory], [Lang_Active]) VALUES (1, N'English', 1, 1, 1)
INSERT [dbo].[Language] ([Lang_Id], [Lang_Name], [Lang_Default], [Lang_Mandatory], [Lang_Active]) VALUES (2, N'Spanish', 0, 0, 1)
INSERT [dbo].[Language] ([Lang_Id], [Lang_Name], [Lang_Default], [Lang_Mandatory], [Lang_Active]) VALUES (3, N'French', 0, 0, 1)
/****** Object: Table [dbo].[CountryTr] Script Date: 02/28/2012 14:21:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryTr]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CountryTr](
[CoTr_Id] [int] NOT NULL,
[CoTr_Coun_Id] [int] NOT NULL,
[CoTr_Lang_Id] [int] NOT NULL,
[CoTr_Name] [varchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
[CoTr_Active] [bit] NOT NULL,
CONSTRAINT [PK_CountryTr] PRIMARY KEY CLUSTERED
(
[CoTr_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (1, 1, 1, N'United States', 1)
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (2, 1, 2, N'Estados Unidos', 1)
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (3, 1, 3, N'Estats Units', 1)
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (4, 2, 1, N'Argentine', 1)
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (5, 2, 2, N'Argentina', 1)
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (6, 2, 3, N'Argentine', 1)
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (7, 3, 1, N'Germany', 1)
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (8, 3, 2, N'Alemania', 1)
INSERT [dbo].[CountryTr] ([CoTr_Id], [CoTr_Coun_Id], [CoTr_Lang_Id], [CoTr_Name], [CoTr_Active]) VALUES (9, 3, 3, N'Alemagne', 0)
/****** Object: StoredProcedure [dbo].[CountryList] Script Date: 02/28/2012 14:21:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[CountryList]
@Language_Id int
AS
BEGIN

SET NOCOUNT ON;

SELECT DISTINCT
c.Coun_Id,
c.Coun_ISO,
ct.CoTr_Name As Coun_Name,
COALESCE(l1.Lang_Id,l2.Lang_Id) AS Lang_Id
FROM
Country c
LEFT JOIN CountryTr ct
ON ct.CoTr_Coun_Id = c.Coun_Id
LEFT JOIN Language l1
ON l1.Lang_Id = ct.CoTr_Lang_Id
CROSS JOIN Language l2
WHERE
COALESCE(l1.Lang_Id,l2.Lang_Id) = @Language_Id
AND
Coun_Active = 1
AND
CoTr_Active = 1

END
'
END
GO
/****** Object: Default [DF_Country_Coun_Active] Script Date: 02/28/2012 14:21:02 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Country_Coun_Active]') AND parent_object_id = OBJECT_ID(N'[dbo].[Country]'))
Begin
ALTER TABLE [dbo].[Country] ADD CONSTRAINT [DF_Country_Coun_Active] DEFAULT ((1)) FOR [Coun_Active]

End
GO
/****** Object: Default [DF_CountryTr_CoTr_Active] Script Date: 02/28/2012 14:21:02 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_CountryTr_CoTr_Active]') AND parent_object_id = OBJECT_ID(N'[dbo].[CountryTr]'))
Begin
ALTER TABLE [dbo].[CountryTr] ADD CONSTRAINT [DF_CountryTr_CoTr_Active] DEFAULT ((1)) FOR [CoTr_Active]

End
GO
/****** Object: Default [DF_Language_Lang_Default] Script Date: 02/28/2012 14:21:02 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Language_Lang_Default]') AND parent_object_id = OBJECT_ID(N'[dbo].[Language]'))
Begin
ALTER TABLE [dbo].[Language] ADD CONSTRAINT [DF_Language_Lang_Default] DEFAULT ((0)) FOR [Lang_Default]

End
GO
/****** Object: Default [DF_Language_Lang_Mandatory] Script Date: 02/28/2012 14:21:02 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Language_Lang_Mandatory]') AND parent_object_id = OBJECT_ID(N'[dbo].[Language]'))
Begin
ALTER TABLE [dbo].[Language] ADD CONSTRAINT [DF_Language_Lang_Mandatory] DEFAULT ((0)) FOR [Lang_Mandatory]

End
GO
/****** Object: Default [DF_Language_Lang_Active] Script Date: 02/28/2012 14:21:02 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_Language_Lang_Active]') AND parent_object_id = OBJECT_ID(N'[dbo].[Language]'))
Begin
ALTER TABLE [dbo].[Language] ADD CONSTRAINT [DF_Language_Lang_Active] DEFAULT ((1)) FOR [Lang_Active]

End
GO
/****** Object: ForeignKey [FK_CountryTr_Country] Script Date: 02/28/2012 14:21:02 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CountryTr_Country]') AND parent_object_id = OBJECT_ID(N'[dbo].[CountryTr]'))
ALTER TABLE [dbo].[CountryTr] WITH CHECK ADD CONSTRAINT [FK_CountryTr_Country] FOREIGN KEY([CoTr_Coun_Id])
REFERENCES [dbo].[Country] ([Coun_Id])
GO
ALTER TABLE [dbo].[CountryTr] CHECK CONSTRAINT [FK_CountryTr_Country]
GO
/****** Object: ForeignKey [FK_CountryTr_Language] Script Date: 02/28/2012 14:21:02 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CountryTr_Language]') AND parent_object_id = OBJECT_ID(N'[dbo].[CountryTr]'))
ALTER TABLE [dbo].[CountryTr] WITH CHECK ADD CONSTRAINT [FK_CountryTr_Language] FOREIGN KEY([CoTr_Lang_Id])
REFERENCES [dbo].[Language] ([Lang_Id])
GO
ALTER TABLE [dbo].[CountryTr] CHECK CONSTRAINT [FK_CountryTr_Language]
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 15:38:14

[code]
CREATE PROCEDURE dbo.CountryList
@Language_Id int
AS

SELECT m.Coun_Id,m.ISO,
COALESCE(ct.Name,df.Name) AS Name,
COALESCE(ct.LangID,df.LangID) AS LangID
FROM
(
SELECT
c.Coun_Id,c.ISO,
l.Lang_Id
FROM Country c
CROSS JOIN Language l
WHERE l.LandID=@IDvalue
)m
LEFT JOIN CountryTr ct
ON ct.CoTr_Coun_Id = m.Coun_Id
AND ct.CoTr_Lang_Id = m.Lang_Id
AND ct.Active=1
CROSS JOIN (SELECT l1.LangId,ct1.Name
FROM Language l1
INNER JOIN CountryTr ct1
ON ct1.CoTr_Lang_Id = l1.Lang_Id
WHERE l1.Default=1
)df
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gudea
Starting Member

18 Posts

Posted - 2012-02-28 : 18:49:42

Thanks visakh for all this effort!

The stored as it is does not compile. Some column names are different from database structure and even parameter name differs. As I understand you may have meant something like this:

ALTER PROCEDURE dbo.CountryList
@Language_Id int
AS

SELECT m.Coun_Id,m.Coun_ISO ,
COALESCE(ct.CoTr_Name,df.CoTr_Name) AS Coun_Name,
COALESCE(ct.CoTr_Lang_Id,df.CoTr_Lang_Id) AS [Lang_ID]
FROM
(SELECT
c.Coun_Id, c.Coun_ISO,
l.Lang_Id
FROM Country c
CROSS JOIN [Language] l
WHERE l.Lang_Id = @Language_Id
)m
LEFT JOIN CountryTr ct
ON ct.CoTr_Coun_Id = m.Coun_Id
AND ct.CoTr_Lang_Id = m.Lang_Id
AND ct.CoTr_Active =1
CROSS JOIN (SELECT l1.Lang_Id ,ct1.CoTr_Name, ct1.CoTr_Lang_Id
FROM [Language] l1
INNER JOIN CountryTr ct1
ON ct1.CoTr_Lang_Id = l1.Lang_Id
WHERE l1.Lang_Default =1
)df

This one compiles, but doesn´t bring the right results.

English listing [CountryList 1] brings:

1 US United States 1
1 US United States 1
1 US United States 1
2 AR Argentine 1
2 AR Argentine 1
2 AR Argentine 1
3 DE Germany 1
3 DE Germany 1
3 DE Germany 1

Where expected:
1 US United States 1
2 AR Argentine 1
3 DE Germany 1

It seems that a DISTINCT would help, and it does, but...

When listing in French (missing Germany´s translation)

CountryList 3

The results are (with DISTINCT):
1 US Estats Units 3
2 AR Argentine 3
3 DE Argentine 1
3 DE Germany 1
3 DE United States 1

Where expected:

1 US Estats Units 3
2 AR Argentine 3
3 DE Germany 1

It is pretty close!! but it seems that when a translation is missing it brings all translations in default language for all countries mixed with the same ISO and Id of the non translated Country.

May be I have corrected wrong the stored.

Thanks
Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 18:57:18

[code]
ALTER PROCEDURE dbo.CountryList
@Language_Id int
AS

SELECT m.Coun_Id,m.Coun_ISO ,
COALESCE(ct.CoTr_Name,df.CoTr_Name) AS Coun_Name,
COALESCE(ct.CoTr_Lang_Id,df.CoTr_Lang_Id) AS [Lang_ID]
FROM
(SELECT
c.Coun_Id, c.Coun_ISO,
l.Lang_Id
FROM Country c
CROSS JOIN [Language] l
WHERE l.Lang_Id = @Language_Id
)m
LEFT JOIN CountryTr ct
ON ct.CoTr_Coun_Id = m.Coun_Id
AND ct.CoTr_Lang_Id = m.Lang_Id
AND ct.CoTr_Active =1
CROSS APPLY(SELECT l1.Lang_Id ,ct1.CoTr_Name, ct1.CoTr_Lang_Id
FROM [Language] l1
INNER JOIN CountryTr ct1
ON ct1.CoTr_Lang_Id = l1.Lang_Id
WHERE l1.Lang_Default =1
AND ctl.CoTr_Coun_Id = m.Coun_Id
)df
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gudea
Starting Member

18 Posts

Posted - 2012-02-29 : 09:21:16

Great!
It worked!
Thanks a lot to everyone.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 12:09:50

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources