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

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_ID, tblCheck.CheckDescr AS Condition,
tblQuestionText.QuestionText
FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
tblQuestionText.Item_ID)
WHERE ((tblQuestionText.LanguageReference = @.Lang_ID) OR
(tblQuestionText.LanguageReference IS NULL))
)

Test data:

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

ALTER TABLE [dbo].[tblCheck] ADD
CONSTRAINT [DF__tblCheck__Creati__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_General_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 ,
[LanguageReference] [int] NOT NULL ,
[QuestionText] [text] COLLATE SQL_Latin1_General_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],
[LanguageReference]
) 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,'English', 1)
INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(2,2,'Espanol', 1)

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

Any tips or pointers will be appreciated. Thanks.Beowulf (beowulf_is_not_here@.hotmail.com) 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_ID, tblCheck.CheckDescr AS Condition,
> tblQuestionText.QuestionText
> FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
> tblQuestionText.Item_ID)
> WHERE ((tblQuestionText.LanguageReference = @.Lang_ID) OR
> (tblQuestionText.LanguageReference 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.LanguageReference = @.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
>> <snip>
>> CREATE FUNCTION Annotated_Check (@.Lang_ID int) RETURNS TABLE AS RETURN (
>> SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,
>> tblQuestionText.QuestionText
>> FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
>> tblQuestionText.Item_ID)
>> WHERE ((tblQuestionText.LanguageReference = @.Lang_ID) OR
>> (tblQuestionText.LanguageReference 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.LanguageReference = @.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.