sqlsql-servert-sqljoinouter-apply

MS SQL SERVER 2008 RS OUTER APPLY and empty RS

Longtime lurker here with my first question.

I have the following tables:

I want to grab everything from Accounts and, for each Accounts record, the most recent Top 1 from Emails and PhoneNumbers where Accounts.ID = Emails.AcctID or Accounts.ID = PhoneNumbers.AcctID.

My RS is currently: SELECT ACCT.* FROM Accounts AS ACCT OUTER APPLY (SELECT TOP 1 E.* FROM Emails AS E WHERE E.E_AcctID = ACCT.ACCT_ID ORDER BY E.E_ID DESC) EM OUTER APPLY (SELECT TOP 1 PH.* FROM PhoneNumbers AS PH WHERE PH.PH_AcctID = ACCT.ACCT_ID ORDER BY PH_ID DESC) PH WHERE ACCT.ACCT_Status > 2;

The problem is that not every account has a corresponding record in Emails or PhoneNumbers.

So, some of the APPLY RS are coming back empty and then I get this RS error: ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal.

Any idea how I can get work around the empty recordsets?

Many thanks in advance. Cheers, Noah

Solution

  • Problem solved!!

    I replaced " ACCT.* " in the beginning of the RS with " * " and that did the trick. I had unintentionally excluded the other tables!

    Thanks to all who looked and commented!