Help with this query, please

[Radu]

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.

[jhofmeyr]

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

[Radu]

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, [e-mail] 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

[jhofmeyr]

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