T-SQL JOIN not bring back valid results but a separate query does

I have a query which works for every order except one. Here's the part that's not working right now:

DECLARE @ordernum INT
SELECT @ordernum = 101257

SELECT  o.CustomerID , ups.*
            From dbo.orders o with (NOLOCK)
      left join (
           Select top 1 UPSAccountInfo.UPSAccount as UPSAccount1
      ,UPSAccountInfo.CID as UPSCID
      ,UPSAccountInfo.Address as  UPSAddress1
      ,UPSAccountInfo.DesiredService  UPSDesiredService1
      ,UPSAccountInfo.Address2 as UPSAddress2
      ,UPSAccountInfo.Suit as UPSSuite
      ,UPSAccountInfo.city as UPSCity
      ,UPSAccountInfo.Country as UPSCountry
      ,UPSAccountInfo.SP as UPSState
      ,UPSAccountInfo.Zip as UPSZip
  FROM UPSAccountInfo
  with (NOLOCK)
   order by date desc
      ) ups on ups.upscid = o.customerid
 WHERE o.OrderNumber = @ordernum

This is part of a larger query, I just pulled out what isn't working. By not working, I mean that it returns the customerid, but none of the UPSAccountInfo. So it is, in fact, bringing back a record.

However, this works just fine:

Select top 1 UPSAccountInfo.UPSAccount as UPSAccount1
      ,UPSAccountInfo.CID as UPSCID
      ,UPSAccountInfo.Address as  UPSAddress1
      ,UPSAccountInfo.DesiredService  UPSDesiredService1
      ,UPSAccountInfo.Address2 as UPSAddress2
      ,UPSAccountInfo.Suit as UPSSuite
      ,UPSAccountInfo.city as UPSCity
      ,UPSAccountInfo.Country as UPSCountry
      ,UPSAccountInfo.SP as UPSState
      ,UPSAccountInfo.Zip as UPSZip
  FROM UPSAccountInfo
  WHERE CID = 58939
   order by date desc

Both the queries have a customerid of 58939, so what's going on?

Any help is appre开发者_开发知识库ciated. This has been working great for several months but now, for this one order, it doesn't. It's driving me nuts.

Oh, and feel free to dump on this code all you want. I didn't write it, I inherited it.

Thanks!

You are selecting TOP 1 in your subquery, but it's not correlated (since it can't be in a JOIN).

So, your newest (TOP 1 ORDER BY DATE DESC = newest) record does not have the same customer id.

As a side note, your queries are not equivalent. Your second query contains a WHERE clause that limits the result set to a single customer, which is not present in the top query.

What if you just use a regular join instead of a subquery join? Like this:

SELECT TOP 1
    o.CustomerID 
    ,ups.UPSAccount as UPSAccount1
   ,ups.CID as UPSCID
   ,ups.Address as  UPSAddress1
   ,ups.DesiredService  UPSDesiredService1
   ,ups.Address2 as UPSAddress2
   ,ups.Suit as UPSSuite
   ,ups.city as UPSCity
   ,ups.Country as UPSCountry
   ,ups.SP as UPSState
   ,ups.Zip as UPSZip
FROM dbo.orders o 
LEFT OUTER JOIN UPSAccountInfo ups
ON ups.cid = o.customerid
WHERE o.OrderNumber = @ordernum
ORDER BY ups.date DESC

If you don't need more than one row from dbo.orders, that should work.