php mysql与左外连接

Question

I have two tables in mysql table1 and table2

table1 have the following fields

Field               Type    

intProjectId        int(11)         
intSourceId     int(11)     
intClientId         int(11)     
varProject      varchar(200)    
fltAmount            float              
varAmountType varchar(50)   
dtStart              date       
dtEnd                date   

And table 2 have the following fields

    Field                                   Type

intPercentageId                     int(11)     
intProjectId                        int(11)         
floatPaymentpercentage              float   
ddDate                              datetime

join two table with common project id.If table2 has no records with the particular project id it can joined as null..

Table 1 has project data Table 2 has its percentage of complettion. (Each project has more than one records. ) Project Id -- common field

Iused the following query

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.intProjectId = table2.intProjectId
GROUP BY table1.varProject ORDER BY table2.intPercentageId DESC

Here i get the output as percentage table returns the first record for each project. I need the last inserted record for the table 2.

table1 ==> project id 5 In table 2 has 3 records for project Id 5. I want to ge the last record from the table2. Now it returns the first record from table 2

How to change the query. Please help me to fix this.

Answer 1

Calculate the maximum record for each project in table2 and use that information to get the latest record. Here is a method that uses group by:

select t1.*
 from table1 t1 join
 table2 t2
 on t1.intProjectId = t2.intProjectId join
 (select t2.intProjectId, max(intPercentageId) as maxpi
  from table2 t2
  group by t2.intProjectId
 ) tt2
 on t2.intProjectId = tt2.maxpi;