1

Longtime lurker here with my first question.

I have the following tables:

  • Accounts
  • Emails
  • PhoneNumbers

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

nbardach
  • 123
  • 1
  • 10
  • 1
    1) tag the dbms being used (i guess it should be sql server) 2) post some sample data 3)post your attempt so far – Vamsi Prabhala Dec 14 '15 at 19:28
  • As @vkp said, more data would be useful. With that said, in my experience joining with a windowed function is faster than an outer apply and should just return nulls. – Andrew O'Brien Dec 14 '15 at 20:48
  • Thanks for the feedback! Will update with your suggestions. Cheers, N – nbardach Dec 14 '15 at 21:22
  • user2538042, how could I use JOINS and still only get the TOP 1 record from each of the joined tables? – nbardach Dec 14 '15 at 21:44

2 Answers2

0

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!

nbardach
  • 123
  • 1
  • 10
0

Assuming that you have a created_datetime column in each table to specify the most recent row we can use the RowNumber function to do the job see this exemple 

declare @ACCOUNTS table (ID_ACC int)
declare @EMAILS table (EMAIL VARCHAR(50), ID_ACC int, CREATED_DATETIME DATETIME)
declare @PHONES table (PHONE VARCHAR(50), ID_ACC int, CREATED_DATETIME DATETIME)


insert into @ACCOUNTS
SELECT '1' ID_ACC
UNION ALL 
SELECT '2' ID_ACC
UNION ALL 
SELECT '3' ID_ACC

INSERT INTO @EMAILS
SELECT 'MyEmail@test.com' EMAIL,'1' ID_ACC, '2015-12-14 22:00:00' CREATED_DATETIME
UNION ALL 
SELECT 'MyNewemail@test.com' EMAIL,'1' ID_ACC, '2015-12-14 22:53:00'
UNION ALL 
SELECT '3rdemail@test.com' EMAIL,'3' ID_ACC, '2015-12-14 22:54:00'
UNION ALL 
SELECT 'NEW3rdemail@test.com' EMAIL,'3' ID_ACC, '2015-12-14 22:55:30'


INSERT INTO @PHONES
SELECT '00213555555' PHONE,'1' ID_ACC, '2015-12-14 22:10:00' CREATED_DATETIME
UNION ALL 
SELECT '00213554444' PHONES,'2' ID_ACC, '2015-12-14 22:54:00'
UNION ALL 
SELECT '00213556666' PHONES,'3' ID_ACC, '2015-12-14 22:54:00'
UNION ALL 
SELECT '00213557777' PHONES,'3' ID_ACC, '2015-12-14 23:10:00'

 SELECT 
 *
 FROM
 (SELECT 
 ROW_NUMBER () over (partition by A.ID_ACC order by E.CREATED_DATETIME DESC,P.CREATED_DATETIME DESC) AS ROW_ORDER,
 A.ID_ACC,
 E.EMAIL,
 P.PHONE
 FROM @ACCOUNTS as A
 left join @EMAILS as E
 on A.ID_ACC=E.ID_ACC
 left join @PHONES as P
 on A.ID_ACC=P.ID_ACC) AS TAB
 WHERE TAB.ROW_ORDER=1
Amine Zaminex
  • 84
  • 3