"SELECT TOP", "LEFT OUTER JOIN", "ORDER BY" gives extra rows
- by Codesleuth
I have the following Access query I'm running through OLE DB in .NET:
SELECT TOP 25
tblClient.ClientCode,
tblRegion.Region
FROM (tblClient LEFT OUTER JOIN
tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region
There are 431 records within tblClient that have RegionCode set to NULL.
For some reason, the query above returns all these 431 records instead of the first 25.
If I change the query to ORDER BY tblClient.Client (the name of the client) like so:
SELECT TOP 25
tblClient.ClientCode,
tblRegion.Region
FROM (tblClient LEFT OUTER JOIN
tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblClient.Client
I get the expected result set of 25 records, showing a mixture of region names and NULL values.
Why is it that ordering by a field retrieved through a LEFT OUTER JOIN will the TOP clause not work?
