Posted by Erland Sommarskog on 11/23/05 00:32

Will Chamberlain (will.chamberlain@devdex.com) writes:
> I am working on queries for a SQL database. I have much experience with
> Access and am trying to ween myself off of it. The query I am working
> on now is supposed to return a set of data like the following:
>
> LatestVersion(Ver)/VersionUpdate(Update)/Location(SheetNumber)
> A/aa1/3
>
> This works fine except for some of the products in the database don't
> have these values attributed to them. I am using the following SQL
> query:
>
> SELECT Max(COALESCE(Sheet.Ver, 'NONE')) AS MaxOfVer,
> Max(COALESCE(Update.Update, 0)) AS MaxOfUpdate, Sheet.SheetNumber FROM
> Drawings INNER JOIN (Update INNER JOIN
> Sheet ON Update.RecordID = Sheet.RecordID) ON Drawings.Drawing =
> Sheet.Drawing WHERE (Sheet.Drawing = 'XXXXX') AND
> (Sheet.SheetType = 'SH') GROUP BY Sheet.Drawing, Sheet.SheetType,
> Sheet.SheetNumber
>
>
> How can I edit this string to return either all 3 of the fields I need,
> or return all of 3 of the fields with value = NONE if there are no
> results. Is it possible to wrap a COALESCE around the whole query
> string?

I'm not sure this makes 100% sense to me. First of all in your current
query you have:

Max(COALESCE(Sheet.Ver, 'NONE'))

Say that there are three entries, and one has the value A1, one the value B1
and one has NULL in Sheet.Ver. You will get NONE as the result. But say now
that a row with the value R1 is added. Now your query will return R1. This
seems funny to me.

As for returning 'NONE' in all three columns when there are no rows
round that fullfils the conditions in the WHERE clause, this also appears
funny as SheetNumber appears to be numeric.

But to get what you want, you could insert the data into a temp table,
and then:

IF NOT EXISTS (SELECT * FROM #temp)
INSERT #temp VALUES ('NONE', 'NONE', 'NONE')
SELECT * FROM #temp

But I am not really sure that is something you should do. Better let
the client handle the fact that there were no hits.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]