Posted by Erland Sommarskog on 11/05/05 21:10

Eugene Anthony (solomon_13000@yahoo.com) writes:
> I done this:
>
> SELECT c.Name, s.Name, t.TaxPerPlace, t.ParentTaxPerPlaceID, MoreRecords
>= (SELECT COUNT(*) FROM #TempItems TI WHERE TI.TaxPerPlaceID >=
> @LastRecord) FROM #TempItems t JOIN Countries c ON c.CountryID =
> t.CountryID JOIN States s ON s.StateID = t.StateId WHERE TaxPerPlaceID >
> @FirstRecord AND TaxPerPlaceID < @LastRecord ORDER BY t.TaxPerPlaceId
>
>
> but a problem I encounter is
>
> CountryID = 0
> StateID = 0
>
> when I dont select a countryID and StateID. 0 is not found in the
> Countries and States table. This would mean c.Name ="N/A" and s.Name =
> "N/A" if its value is 0. How is it done in my case.

That's an akward design, since it prevents you from setting up a
foreign-key consraint to the Country and State tables. Better to use
NULL for cases when the state/country for some reason is not applicable.

Using NULL would not really change your problem though. To that end,
you need to do an outer join:

SELECT Country = coalesce(c.Name, 'N/A'),
State = coalesce(s.Name, 'N/A'),
t.TaxPerPlace, t.ParentTaxPerPlaceID, cnt.MoreRecords
FROM #TempItems t
LEFT JOIN Countries c ON c.CountryID = t.CountryID
LEFT JOIN States s ON s.StateID = t.StateId
CROSS JOIN (SELECT MoreRecords = COUNT(*)
FROM #TempItems
WHERE TaxPerPlaceID >= @LastRecord) AS cnt
WHERE TaxPerPlaceID > @FirstRecord
AND TaxPerPlaceID < @LastRecord ORDER BY t.TaxPerPlaceId

With an outer join, all rows from the outer table are retain, and
SQL Server placs NULL values in the columns where there are no matching
Countries or States. The coalesce function takes a list of values as
argument, and returns the first non-NULL value in the list.

While I was at it, I rearranged the computation of MoreRecords to be
more efficient. (In your query it is computed once for every returned
row.) A cross join, is a cartesian product, and something you rarely
want, but in this case the cross-join is with a one-row table so that
is alright. And that table is a so-called derived table; that is a
SELECT query in the acts as a temporary table from a logical point of
view.

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

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

[Back to original message]