October 20, 2015 at 7:26 am
Hopefully someone out there can help me.
I have a table of languages, identified by a lang_id column as follows:
LANG_IDLANG_NAME
deDeutsche
enEnglish
plPolski
trTurkish
czCzech
I also have a RESOURCES table of phrases (for translation purposes) similar to this:
res_id res_lang res_phrase
AccessDenied en Access Denied
...
For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.
My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.
For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.
Thanks
October 20, 2015 at 7:32 am
Is the Res_id unique in the resources table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2015 at 7:33 am
Hi, no I'm afraid not. The combination of res_id and res_lang would be though.... but of course, some are missing and these I'm trying to identify.
October 20, 2015 at 7:39 am
Could you perhaps post CREATE TABLE statements for each of the tables, and some sample data as INSERT statements?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2015 at 7:50 am
OK, here is some example data and schema:
/****** Object: Table [dbo].[LANGUAGES] Script Date: 20/10/2015 14:47:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LANGUAGES](
[LANG_ID] [nvarchar](10) NOT NULL,
[LANG_NAME] [nvarchar](50) NOT NULL,
[LANG_FLAG] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[RESOURCES] Script Date: 20/10/2015 14:47:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RESOURCES](
[RES_ID] [nvarchar](200) NOT NULL,
[RES_LANG] [nvarchar](10) NOT NULL,
[RES_PHRASE] [nvarchar](max) NOT NULL,
[RES_NOTES] [ntext] NULL,
CONSTRAINT [PK_RESOURCES] PRIMARY KEY CLUSTERED
(
[RES_ID] ASC,
[RES_LANG] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[LANGUAGES] ([LANG_ID], [LANG_NAME], [LANG_FLAG]) VALUES (N'de', N'Deutsche', NULL)
INSERT [dbo].[LANGUAGES] ([LANG_ID], [LANG_NAME], [LANG_FLAG]) VALUES (N'en', N'English', NULL)
INSERT [dbo].[LANGUAGES] ([LANG_ID], [LANG_NAME], [LANG_FLAG]) VALUES (N'pl', N'Polski', NULL)
INSERT [dbo].[LANGUAGES] ([LANG_ID], [LANG_NAME], [LANG_FLAG]) VALUES (N'tr', N'Turkish', NULL)
INSERT [dbo].[LANGUAGES] ([LANG_ID], [LANG_NAME], [LANG_FLAG]) VALUES (N'cz', N'Czech', NULL)
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.cbOK.Text', N'cz', N'OK', N'Used for the OK button label on the access denied page')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.cbOK.Text', N'de', N'OK', NULL)
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.cbOK.Text', N'en', N'OK', N'Used for the OK button label on the access denied page')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.cbOK.Text', N'pl', N'OK', N'Used for the OK button label on the access denied page')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.cbOK.Text', N'tr', N'Tamam', NULL)
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.lblAccessDeniedMsgText.Text', N'cz', N'Prístup odepren. Kontaktujte prosím svého administrátora.', N'Used for the access denied message on the access denied page')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.lblAccessDeniedMsgText.Text', N'en', N'Permission denied. Please contact your administrator for assistance.', N'Used for the access denied message on the access denied page')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.lblAccessDeniedMsgText.Text', N'pl', N'Odmowa dostepu. Skontaktuj sie z administratorem.', N'Used for the access denied message on the access denied page')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AccessDenied.lblAccessDeniedMsgText.Text', N'tr', N'Izin verilmedi. Lütfen yardim için yöneticinizle irtibat kurun.', NULL)
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AdminMenuTitle', N'cz', N'Administrace', N'Text for the admin sub menu title')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AdminMenuTitle', N'de', N'Administration', N'Text for the admin sub menu title')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AdminMenuTitle', N'en', N'Administration', N'Text for the admin sub menu title')
INSERT [dbo].[RESOURCES] ([RES_ID], [RES_LANG], [RES_PHRASE], [RES_NOTES]) VALUES (N'AdminMenuTitle', N'tr', N'Yönetim', N'Text for the admin sub menu title')
Basically I want to identify and RESOURCES.res_id that do not have an entry for every LANGUAGES.lang_id.
Many thanks
October 20, 2015 at 7:59 am
This should work
WITH ResourcesMissingALanguage AS
(SELECT res_id
FROM RESOURCES GROUP BY res_id HAVING COUNT(*) < (SELECT COUNT(*) FROM LANGUAGES)
)
SELECT Res_id, Lang_id FROM ResourcesMissingALanguage rml CROSS JOIN Languages l
WHERE NOT EXISTS (SELECT 1 FROM Resources r WHERE rml.res_id = r.res_id AND r.RES_LANG = l.Lang_id)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2015 at 8:02 am
Many thanks, exactly what I needed 🙂
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply