SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[Language]
(
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [Name] NVARCHAR(256) NOT NULL,
    [Culture] NVARCHAR(10) UNIQUE NOT NULL,
    [DateCreated] DATETIME NOT NULL DEFAULT GETUTCDATE(),
    CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE Book (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    [PublicDate] DATETIME DEFAULT GETUTCDATE()
)

CREATE TABLE [BookLanguage] (
    [BookId] UNIQUEIDENTIFIER NOT NULL,
    [LanguageId] UNIQUEIDENTIFIER NOT NULL,
    [Default] BIT NOT NULL,
    CONSTRAINT [PK_BookLanguage] PRIMARY KEY CLUSTERED ([BookId] ASC, [LanguageId] ASC),
    CONSTRAINT [FK_BookLanguage_Book] FOREIGN KEY ([BookId]) REFERENCES [Book]([Id]),
    CONSTRAINT [FK_BookLanguage_Language] FOREIGN KEY ([LanguageId]) REFERENCES [Language]([Id])
)

CREATE TABLE BookLocalization(
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
    [BookId] UNIQUEIDENTIFIER NOT NULL,
    [Name] NVARCHAR(256),
    [LanguageId] UNIQUEIDENTIFIER,
    CONSTRAINT [FK_BookLocalization_Book] FOREIGN KEY ([BookId]) REFERENCES [Book]([Id])
)

INSERT INTO [Language]([Id], [Name], [Culture], [DateCreated])
VALUES ('2F12CAA6-16D7-4D83-B17E-560241DAE1D2', 'English', 'en', GETUTCDATE())

INSERT INTO [Language]([Id], [Name], [Culture], [DateCreated])
VALUES ('895E0F72-413C-48CD-A1A1-6302AC8A4CB4', 'Spanish', 'es', GETUTCDATE())

INSERT INTO [Book] ([Id], [PublicDate])
VALUES('D31A6823-5415-407F-9B49-49136242F03F', GETUTCDATE())

INSERT INTO [BookLanguage] ([BookId], [LanguageId], [Default])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', '2F12CAA6-16D7-4D83-B17E-560241DAE1D2', 1)

INSERT INTO [BookLanguage] ([BookId], [LanguageId], [Default])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', '895E0F72-413C-48CD-A1A1-6302AC8A4CB4', 0)

INSERT INTO [BookLocalization] ([BookId], [Name], [LanguageId])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', 'My First Book', '2F12CAA6-16D7-4D83-B17E-560241DAE1D2')

INSERT INTO [BookLocalization] ([BookId], [Name], [LanguageId])
VALUES ('D31A6823-5415-407F-9B49-49136242F03F', 'Mi Primer Libro', '895E0F72-413C-48CD-A1A1-6302AC8A4CB4')
7 rows affected

select * from Book
select * from BookLanguage
select * from BookLocalization
select * from [Language]


Id
PublicDate


d31a6823-5415-407f-9b49-49136242f03f
2023-07-06 20:26:25.217




BookId
LanguageId
Default


d31a6823-5415-407f-9b49-49136242f03f
2f12caa6-16d7-4d83-b17e-560241dae1d2
True

d31a6823-5415-407f-9b49-49136242f03f
895e0f72-413c-48cd-a1a1-6302ac8a4cb4
False




Id
BookId
Name
LanguageId


1
d31a6823-5415-407f-9b49-49136242f03f
My First Book
2f12caa6-16d7-4d83-b17e-560241dae1d2

2
d31a6823-5415-407f-9b49-49136242f03f
Mi Primer Libro
895e0f72-413c-48cd-a1a1-6302ac8a4cb4




Id
Name
Culture
DateCreated


2f12caa6-16d7-4d83-b17e-560241dae1d2
English
en
2023-07-06 20:26:25.200

895e0f72-413c-48cd-a1a1-6302ac8a4cb4
Spanish
es
2023-07-06 20:26:25.200


declare @LanguageId uniqueidentifier = '895E0F72-413C-48CD-A1A1-6302AC8A4CB4';

select top 1 b.[Id], b.[PublicDate], 
    blz.[Id] AS BookLocalizationId, blz.[Name], blz.[LanguageId],
    blg.[LanguageId], blg.[Default]
from Book b
inner join BookLocalization blz on blz.BookId = b.id
inner join BookLanguage blg on blg.BookId = b.Id
    and blg.LanguageId = blz.LanguageId
order by case when @LanguageId = blz.LanguageId then 1 else 0 end desc
    , blg.[Default] desc;


Id
PublicDate
BookLocalizationId
Name
LanguageId
LanguageId
Default


d31a6823-5415-407f-9b49-49136242f03f
2023-07-06 20:26:25.217
2
Mi Primer Libro
895e0f72-413c-48cd-a1a1-6302ac8a4cb4
895e0f72-413c-48cd-a1a1-6302ac8a4cb4
False

BookId	LanguageId	Default
d31a6823-5415-407f-9b49-49136242f03f	2f12caa6-16d7-4d83-b17e-560241dae1d2	True
d31a6823-5415-407f-9b49-49136242f03f	895e0f72-413c-48cd-a1a1-6302ac8a4cb4	False

Id	BookId	Name	LanguageId
1	d31a6823-5415-407f-9b49-49136242f03f	My First Book	2f12caa6-16d7-4d83-b17e-560241dae1d2
2	d31a6823-5415-407f-9b49-49136242f03f	Mi Primer Libro	895e0f72-413c-48cd-a1a1-6302ac8a4cb4

Id	Name	Culture	DateCreated
2f12caa6-16d7-4d83-b17e-560241dae1d2	English	en	2023-07-06 20:26:25.200
895e0f72-413c-48cd-a1a1-6302ac8a4cb4	Spanish	es	2023-07-06 20:26:25.200