Another select statement question

[cb49747]

here is a query I run

SELECT loaninfo.loannumber, loaninfo.b1_lastname, loaninfo.nextduedate, loaninfo.lastpaymentdate, achtransfer.paymentdate, achtransfer.total_amount, loaninfo.investornumber FROM loaninfo LEFT JOIN achtransfer ON loaninfo.loannumber=achtransfer.loannumber WHERE loaninfo.investornumber NOT IN ('001', '002', '003') AND loaninfo.totaldelinquent > '0' AND loaninfo.upb > '0'

I then get the results run them thru a loop and run this query on every result of the previous query. $lnb is the loannumber selected in the above query.

SELECT commentdate, comment FROM comments WHERE loannumber = '$lnb' ORDER BY commentdate DESC LIMIT 0, 5

I then display the results of the first query with the last five comments for that loan.

this works but is slow. I'm wondering if there is a way to combine the two queries? Not sure how I would do this because of the multiple records i need to display in the second query.

Thanks for any help with this

 

[r937]

SELECT loaninfo.loannumber
     , loaninfo.b1_lastname
     , loaninfo.nextduedate
     , loaninfo.lastpaymentdate
     , achtransfer.paymentdate
     , achtransfer.total_amount
     , loaninfo.investornumber 
     , [red]comments.commentdate[/red]
     , [red]comments.comment[/red]
  FROM loaninfo 
LEFT 
  JOIN achtransfer 
    ON achtransfer.loannumber = loaninfo.loannumber
[red]LEFT OUTER
  JOIN comments 
    ON comments.loannumber = loaninfo.loannumber[/red]
 WHERE loaninfo.investornumber 
         NOT IN ('001', '002', '003') 
   AND loaninfo.totaldelinquent > '0' 
   AND loaninfo.upb > '0'

r937.com | rudy.ca

 

[cb49747]

this works however it gives me all the comments when I only want the last five

 

[cb49747]

I'm looking to get all the information in the first query plus the last 5 comments for each loan selected in the first query.

The first query will return about 9000 records

I was hoping there was some function like

Left Join comments on loaninfo.loannumber=comments.loannumber LIMIT 0, 5

but the above just returns an error for me.

Anybody else have any ideas?

 

[r937]

what's the average number of comments? if it's like 7 per loan, then you should probably just sort the results from the query i gave you and only print the first 5 for each loan, ignoring the others in the results

if it's more like 187 comments per loan, then of course you'll want to restrict it to 5 with SQL

i should warn you, however, that limiting it to 5 comments per loan is a ~lot~ less efficient, the SQL is rather complex

your call

r937.com | rudy.ca

 

[cb49747]

I'm dealing with anywhere from 50 to 200 comments per loan. So it is best to use sql to trim it to five. I can see how the sql can get complex. That is why I was using the two queries, this just takes a long time. But if you could give me an idea on how to start with this I would be greatly appreciated.

Thanks a bunch for all your help.

 

[r937]

SELECT loaninfo.loannumber
     , loaninfo.b1_lastname
     , loaninfo.nextduedate
     , loaninfo.lastpaymentdate
     , achtransfer.paymentdate
     , achtransfer.total_amount
     , loaninfo.investornumber 
     , [red]c[/red].commentdate
     , [red]c[/red].comment
  FROM loaninfo 
LEFT 
  JOIN achtransfer 
    ON achtransfer.loannumber = loaninfo.loannumber
LEFT OUTER
  JOIN comments [red] AS c[/red]
    ON [red]c[/red].loannumber = loaninfo.loannumber
   [red]AND ( SELECT COUNT(*) 
           FROM comments  
          WHERE loannumber = loaninfo.loannumber
            AND commentdate > c.commentdate  ) < 5[/red]
 WHERE loaninfo.investornumber 
         NOT IN ('001', '002', '003') 
   AND loaninfo.totaldelinquent > '0' 
   AND loaninfo.upb > '0'

r937.com | rudy.ca