select coalesce(AppSnippets.value,Snippets.Value),
<otherColumns>
from Snippets left join AppSnippets
on AppSnippets.SnippetsId = Snippets.Id
and AppSnippets.AppId = @appid
where Snippets.LanguageId = @languageid
Frågan#1
SQLServer 2005
Vill ta ut alla från Snippets där Snippets.LanguageId = @languageid, men om det skulle finnas en post i AppSnippets där AppSnippets.SnippetsId = Snippets.Id och AppSnippets.AppId = @appid, så skall AppSnippets.Value returneras istället för Snippets.Value.
CREATE TABLE [Snippets] (
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[LanguageId] [int] NOT NULL ,
[Key] [varchar] (50) NOT NULL ,
[Value] [nvarchar] (1000) NOT NULL ,
CONSTRAINT [PK_Snippets] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AppSnippets] (
[SnippetsId] [int] NOT NULL ,
[AppId] [int] NOT NULL ,
[Value] [nvarchar] (1000) NOT NULL ,
CONSTRAINT [PK_AppSnippets] PRIMARY KEY CLUSTERED
(
[SnippetsId],
[AppId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Snippets]([LanguageId], [Key], [Value]) VALUES(1,'aaa','defaultstring_aaa')
INSERT INTO [dbo].[Snippets]([LanguageId], [Key], [Value]) VALUES(1,'bbb','defaultstring_bbb')
INSERT INTO [dbo].[Snippets]([LanguageId], [Key], [Value]) VALUES(1,'ccc','defaultstring_ccc')
INSERT INTO [dbo].[AppSnippets]([SnippetsId], [AppId], [Value]) VALUES(2,1,'customstring_bbb')