sql server (alternate): [posted and mailed, please reply in news]

PipHans (piphans@hotmail.com) writes:
[quoted text, click to view]

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, sommar@algonet.se

Books Online for SQL Server SP3 at

PipHans (piphans@hotmail.com) writes:
[quoted text, click to view]

Yes. Sorry about the typo.

[quoted text, click to view]

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.

[quoted text, click to view]

For this query, just change WHERE to AND, yes.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

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

[quoted text, click to view]

Yes. Thats what I want

[quoted text, click to view]

Yes...this is the essence of my "problem". However I still want those
entries in Template that hasnt got a corresponding entry in TValues....

[quoted text, click to view]

True...this does actually return what I want.

[quoted text, click to view]

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. ?

[quoted text, click to view]

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

/Pip