Hi. I have a table, let's name it TableA, containing a list of PINS
with some other data. I have another table, "Comments". A PIN in
TableA might have 0, 1, or many records in the Comments table. I need
to produce a data set for a report. My query would be:
__________________________________________
Select *

from dbo.[TableA] LEFT OUTER JOIN (SELECT EmployeePIN,
Count(EmployeePIN) AS OldComments FROM dbo.Comments GROUP BY
EmployeePIN ) X

ON ([TableA].PIN = X.EmployeePIN)
__________________________________________

which works fine. The only problems is that I would like to show some
string, like 'No Records' , if the field OldComments returns NULL.

So I try:
__________________________________________

Select [TableA].*, CASE X.OldComments WHEN NULL THEN 'No Records' END
AS Comments

from dbo.[TableA] LEFT OUTER JOIN (SELECT EmployeePIN,
Count(EmployeePIN) AS OldComments FROM dbo.Comments GROUP BY
EmployeePIN ) X

ON ([TableA].PIN = X.EmployeePIN)
__________________________________________

which works just as well, except that it does not return 'No
Comments', or 0, or... whatever, when it is null. I need to show
something NOT NULL in the dataset, so I can easily build a link with
that something. A NULL linking to another page works, but you, as a
user, have to KNOW that you need to click there - I would like to
show, say, an underlined 'No Records' instead...

What should I do, please ?

Thank you for reading this.
Alex.

Hi Alex,

There are a couple of ways you could do this, but your first step
should be to look up the ISNULL function in BOL

Another thing - as you are returning a COUNT value (Integer), you will
never be able to display 'No Comments' (String) as a result for this
column as it is not the same type. You'll have to settle for 0, or
convert the valid counts to strings which is probably not a good idea
for your downstream logic.

So sticking as close to your supplied query as possible, try:
/* NOTE: All queries untested as no DDL supplied */
SELECT
[TableA].*
, CASE
WHEN X.OldComments IS NULL
THEN 0
ELSE X.OldComments
END AS Comments
FROM dbo.[TableA]
LEFT OUTER JOIN (
SELECT
EmployeePIN
, COUNT(EmployeePIN) AS OldComments
FROM dbo.Comments
GROUP BY EmployeePIN
) X
ON [TableA].PIN = X.EmployeePIN

Another way using ISNULL:
SELECT
[TableA].*
, ISNULL(X.OldComments, 0) AS Comments
FROM dbo.[TableA]
LEFT OUTER JOIN (
SELECT
EmployeePIN
, COUNT(EmployeePIN) AS OldComments
FROM dbo.Comments
GROUP BY EmployeePIN
) X
ON [TableA].PIN = X.EmployeePIN

My preferred way:
SELECT
[TableA].*
, COUNT(c.EmployeePIN) AS Comments
FROM dbo.[TableA]
LEFT OUTER JOIN dbo.Comments c
ON [TableA].PIN = c.EmployeePIN
GROUP BY <[TableA].*> -- Column list not supplied

Good luck!
J

Thank you very much, "J", you have been very kind. Problem solved, and
I have learned something.
Thanks again ! Alex.

On Dec 11, 7:14 am, jhofm...@googlemail.com wrote:
> Hi Alex,
>
> There are a couple of ways you could do this, but your first step
> should be to look up the ISNULL function in BOL
>
> Another thing - as you are returning a COUNT value (Integer), you will
> never be able to display 'No Comments' (String) as a result for this
> column as it is not the same type. You'll have to settle for 0, or
> convert the valid counts to strings which is probably not a good idea
> for your downstream logic.
>
> So sticking as close to your supplied query as possible, try:
> /* NOTE: All queries untested as no DDL supplied */
> SELECT
> [TableA].*
> , CASE
> WHEN X.OldComments IS NULL
> THEN 0
> ELSE X.OldComments
> END AS Comments
> FROM dbo.[TableA]
> LEFT OUTER JOIN (
> SELECT
> EmployeePIN
> , COUNT(EmployeePIN) AS OldComments
> FROM dbo.Comments
> GROUP BY EmployeePIN
> ) X
> ON [TableA].PIN = X.EmployeePIN
>
> Another way using ISNULL:
> SELECT
> [TableA].*
> , ISNULL(X.OldComments, 0) AS Comments
> FROM dbo.[TableA]
> LEFT OUTER JOIN (
> SELECT
> EmployeePIN
> , COUNT(EmployeePIN) AS OldComments
> FROM dbo.Comments
> GROUP BY EmployeePIN
> ) X
> ON [TableA].PIN = X.EmployeePIN
>
> My preferred way:
> SELECT
> [TableA].*
> , COUNT(c.EmployeePIN) AS Comments
> FROM dbo.[TableA]
> LEFT OUTER JOIN dbo.Comments c
> ON [TableA].PIN = c.EmployeePIN
> GROUP BY <[TableA].*> -- Column list not supplied
>
> Good luck!
> J

You're welcome

If you're using SQL Server 2005, you should also do a quick search on
Common Table Expressions (CTE's) in BOL.

J