How to get only 1 or 2 records on many to many table relation based on default column and passed language id

Question

Please consider the following tables and data that is inserted:

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')

I am trying to figure out how to select the book that the user wants with its localization record and if the language does not exist for selected book, then to return the default language that is indicated for that book.

For example, the book has english and spanish records. So if I pass in the english language Id then I get english. If I pass the spanish language id then I get spanish. If I pass french language id, then I get english because the book has no french language.

Here is what I tried:

SELECT B.[Id], B.[PublicDate], 
    BL.[Id] AS BookLocalizationId, BL.[Name], BL.[LanguageId],
    BL2.[LanguageId], BL2.[Default]
FROM [Book] B
    INNER JOIN [BookLocalization] BL ON BL.[BookId] = B.[Id]
    INNER JOIN [BookLanguage] BL2 ON BL2.[BookId] = B.[Id]

The problem with the above is that it returns 4 records. I don't fully understand exactly how I end up with 4 records and I am hoping somebody can explain why and tell me how I can achieve returning only 1 record. Or if one not possible, then two. 1st being the language I selected, 2nd being the default language for that table and then I can in code pick which record to return to user.

Edit: BookLanguage represents all languages available/allowed for that book. Some books are translated in 2 languages, other in 5. That table controls what languages are allowed for the book to be translated in. BookLocalization is the actual translation.

Answer 1

Your join of book language is not specific enough, you need to also join on language, else you get the cross-join effect you were experiencing.

And to get either the specific language or the default language, you can use TOP 1 with ORDER BY where you order by those 2 conditions.

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 = blz.BookId
    and blg.LanguageId = blz.LanguageId
order by case when @LanguageId = blz.LanguageId then 1 else 0 end desc
    , blg.[Default] desc;

DbFiddle

Joining notes: When you join 1 Book with 2 BookLocalization rows you get 2 rows (i.e. Books * BookLocalizations). When you come to join BookLanguage on, for which you have another 2 rows, if you only join with the BookId you get 4 rows, because each BookLanguage row matches both BookLocalizations rows (i.e. Books * BookLocalizations * BookLanguages). But what you actually want is to match a BookLocalization with a BookLanguage on both BookId and LanguageId.

DbFiddle