Problem joining child data (JOIN, subquery, or something else?)

[Beowulf]

The problem:
I'm updating a report to be "multi-language" capable. Previously,
any items that had text associated with them were unconditionally
pulling in the English text. The database has always been capable of
storing multiple languages for an item, however.

Desired output:
Given the test data below, I'd like to get the following results

select * from mytestfunc(1)
Item_Id, Condition, QuestionText
1876, NOfKids <= 10, This many children is unlikely.

select * from mytestfunc(2)
CheckID, Condition, QuestionText
1876, NOfKids <= 10, NULL

The current SQL for my UDF:
CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (
SELECT tblCheck.Item_I D, tblCheck.CheckD escr AS Condition,
tblQuestionText .QuestionText
FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ ID =
tblQuestionText .Item_ID)
WHERE ((tblQuestionTe xt.LanguageRefe rence = @Lang_ID) OR
(tblQuestionTex t.LanguageRefer ence IS NULL))
)

Test data:

CREATE TABLE [dbo].[tblCheck] (
[Item_ID] [int] NOT NULL ,
[CheckDescr] [text] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCheck] ADD
CONSTRAINT [DF__tblCheck__C reati__0D7A0286] DEFAULT (getdate()) FOR
[CreationDate],
CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED
(
[Item_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLanguage] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Priority] [int] NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[Spoken] [bit] NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADD
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLanguage] ADD
CONSTRAINT [DF__tblLangua__ Creat__2CF2ADDF] DEFAULT (getdate()) FOR
[CreationDate],
UNIQUE NONCLUSTERED
(
[Priority]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOCREATE TABLE [dbo].[tblQuestionText] (
[Item_ID] [int] NOT NULL ,
[LanguageReferen ce] [int] NOT NULL ,
[QuestionText] [text] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[SameAs] [int] NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblQuestionText] ADD
CONSTRAINT [DF__tblQuesti__ Creat__76969D2E] DEFAULT (getdate()) FOR
[CreationDate],
CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED
(
[Item_ID],
[LanguageReferen ce]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

INSERT INTO tblCheck (Item_ID, CheckDescr)
VALUES(1876, 'NOfKids <= 10')

INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(1,1,'Eng lish', 1)
INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(2,2,'Esp anol', 1)

INSERT INTO tblQuestionText (Item_ID, LanguageReferen ce, QuestionText)
VALUES (1876, 1, 'This many children is unlikely.')

Any tips or pointers will be appreciated. Thanks.

[Erland Sommarskog]

Beowulf (be************ *****@hotmail.c om) writes:

I'm updating a report to be "multi-language" capable. Previously,
any items that had text associated with them were unconditionally
pulling in the English text. The database has always been capable of
storing multiple languages for an item, however.

Desired output:
Given the test data below, I'd like to get the following results
Thanks for the extensive repro!

Then again, the fix is simple:
CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (
SELECT tblCheck.Item_I D, tblCheck.CheckD escr AS Condition,
tblQuestionText .QuestionText
FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ ID =
tblQuestionText .Item_ID)
WHERE ((tblQuestionTe xt.LanguageRefe rence = @Lang_ID) OR
(tblQuestionTex t.LanguageRefer ence IS NULL))
)

Change WHERE to AND and skip last condition on IS NULL:

CREATE FUNCTION mytestfunc (@Lang_ID int) RETURNS TABLE AS RETURN (
SELECT C.Item_ID, C.CheckDescr AS Condition, QT.QuestionText
FROM tblCheck C
LEFT JOIN tblQuestionText QT ON C.Item_ID = QT.Item_ID
AND QT.LanguageRefe rence = @Lang_ID
)

This is a classic error on the left join operator - yes, I did it
too! But once you understand it, it's apparent:

The whole FROM JOIN forms a table which is then filtered by WHERE.
In this case the LEFT JOIN as you had written it, never produced
any rows with NULL in the QuestionText columns, as there was a match
for all. But when you move the condition on language to the ON
clause, you only get a matych if the language is the desired one.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Beowulf]

Erland Sommarskog wrote:

<snip>
CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (
SELECT tblCheck.Item_I D, tblCheck.CheckD escr AS Condition,
tblQuestionText .QuestionText
FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ ID =
tblQuestionText .Item_ID)
WHERE ((tblQuestionTe xt.LanguageRefe rence = @Lang_ID) OR
(tblQuestionTex t.LanguageRefer ence IS NULL))
)

Change WHERE to AND and skip last condition on IS NULL:

CREATE FUNCTION mytestfunc (@Lang_ID int) RETURNS TABLE AS RETURN (
SELECT C.Item_ID, C.CheckDescr AS Condition, QT.QuestionText
FROM tblCheck C
LEFT JOIN tblQuestionText QT ON C.Item_ID = QT.Item_ID
AND QT.LanguageRefe rence = @Lang_ID
)

This is a classic error on the left join operator - yes, I did it
too! But once you understand it, it's apparent:

The whole FROM JOIN forms a table which is then filtered by WHERE.
In this case the LEFT JOIN as you had written it, never produced
any rows with NULL in the QuestionText columns, as there was a match
for all. But when you move the condition on language to the ON
clause, you only get a matych if the language is the desired one.

Wow. My assumption was that I was going to have to get into some heavy
duty SQL hackery, but it really is quite simple. This even works
correctly if there actually is Spanish text. I had come up with
something of a workaround that would return NULL for me for other
languages if the only text was English, but it returned multiple records
if there was English and Spanish.

Thanks so much for the reply.