where clause messed up

[PipHans]

Hi,

Consider this (light example):

SELECT [template].[id], [template].[navn], [tvalues].[id]
FROM template LEFT JOIN Tvalues ON [tvalues].[templateid]=[template].[id]

This returns ok. But I have a clause on the Tvalues like this:

WHERE [tvalues].[nr]=1;

Now I dont get what I want anymore? What I mean here is:
"return all records from Template and the corresponding values in TValues.
If no corresponding values can be found or those where TValues.nr<>1 then
return NULL".

Erhm...`Hope u get my meaning...

/Pip

[Erland Sommarskog]

[posted and mailed, please reply in news]

PipHans (pi*****@hotmail.com) writes:

Consider this (light example):

SELECT [template].[id], [template].[navn], [tvalues].[id]
FROM template LEFT JOIN Tvalues ON [tvalues].[templateid]=[template].[id]

This returns ok. But I have a clause on the Tvalues like this:

WHERE [tvalues].[nr]=1;

Now I dont get what I want anymore? What I mean here is:
"return all records from Template and the corresponding values in TValues.
If no corresponding values can be found or those where TValues.nr<>1 then
return NULL".

But what you are saying is:

Construct a table of template and Tvalues. If there is no matching
rows in Tvalues for a template, put NULL in all columns for Tvalues.

The you filter this table to only show rows where Tvalues.nr is 1.
As NULL is not 1 those rows are moved away.

To get the result you want there three possibilities:

1) WHERE tvalues.nr = 1 OR tvalues.nr IS NULL
2) WHERE coalesce(t.values.nr, 1) = 1
3) Move the condition to the ON clause.

The last alternative, which what I prefer, changes the constructed
table to only include rows from Tvalues when nr = 1. With other
values in Tvalues, the Tvalues columns will be NULL.

Note: this constructed table is a logical concept and does not relate
to how the optimizer implements the query.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[PipHans]

Erland Sommarskog wrote:

But what you are saying is:

Construct a table of template and Tvalues. If there is no matching
rows in Tvalues for a template, put NULL in all columns for Tvalues.
Yes. Thats what I want
The you filter this table to only show rows where Tvalues.nr is 1.
As NULL is not 1 those rows are moved away.
Yes...this is the essence of my "problem". However I still want those
entries in Template that hasnt got a corresponding entry in TValues....
To get the result you want there three possibilities:

1) WHERE tvalues.nr = 1 OR tvalues.nr IS NULL
True...this does actually return what I want.
2) WHERE coalesce(t.values.nr, 1) = 1
You do mean "coalesce(TValues.nr, 1)" right?
Could you explain this function in "english" :) ?
The explanation in my msdn isnt that good. Something like
Return Tvalues.nr if Tvalues.nr is NULL - otherwise return 1. ?
3) Move the condition to the ON clause.`
The last alternative, which what I prefer, changes the constructed
table to only include rows from Tvalues when nr = 1. With other
values in Tvalues, the Tvalues columns will be NULL.

I dont get this one? You just mean "...AND TValues.nr=1"?

/Pip

[Erland Sommarskog]

PipHans (pi*****@hotmail.com) writes:

2) WHERE coalesce(t.values.nr, 1) = 1
You do mean "coalesce(TValues.nr, 1)" right?

Yes. Sorry about the typo.
Could you explain this function in "english" :) ?

coalesce() accepts a list of values as arguments, and returns the
first non-NULL value in the list, or NULL if all arguments have
the value NULL.

isnull() is an older function with a clearer name, but it only accepts
two arguments. coalesce() is ANSI standard.

3) Move the condition to the ON clause.`
The last alternative, which what I prefer, changes the constructed
table to only include rows from Tvalues when nr = 1. With other
values in Tvalues, the Tvalues columns will be NULL.

I dont get this one? You just mean "...AND TValues.nr=1"?

For this query, just change WHERE to AND, yes.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp