Is there a better way to write this query?

[cb49747]

I have three tables
table1, table2, table3

Tables 1 and 2 will contain only one match in each, however table 3 could have multiple matches, and I only want the last five.

Here is how I do it. The $criteria is just my criteria and only pertains to table1

SELECT table1.loannumber, table1.altloannumber, table1.nextduedate, table2.paymentdate, table2.total_amount
FROM table1 LEFT JOIN table2 ON table1.loannumber=table2.loannumber WHERE $criteria

upon getting this data I run a loop in which I run this query every time. Hence the $row[0] holds the loannumber gathered in first query.

SELECT transactiondate, transactionamount FROM table3 WHERE transactioncode IN ('01', '02', '11') AND loannumber = '$row[0]' ORDER BY `transactionnumber` DESC LIMIT 0 , 5

I then print a record f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15 where f1 thru f5 are the 5 fields selected in query 1 and f6 thru f15 are the two fields selected 5 times in query 2.

Now query1 will get about 800 results, so I run the second query 800 times. It takes a little time to run the whole process. I was hoping there may be a faster way to do it, like incorporating everything in one query?

Appreciate any ideas.

Chris Burger

 

[Bastien]

hard to do without sample data structures...

i would try something like

SELECT table1.loannumber, table1.altloannumber, table3.transactiondate, table3.transactionamount, table1.nextduedate, table2.paymentdate, table2.total_amount,
FROM table1 LEFT JOIN table2 ON table1.loannumber=table2.loannumber
inner join table3 on table1.loadnnumber WHERE $criteria


Bastien

Cat, the other other white meat