如何使用Join将最后插入的单个列与子表中的多个值进行匹配

Question

I have one table jobs in my database. and its two child tables. one job_status_detail and job_assigned.

One job may have multiple status like created,started,completed in job_status_detail with one job_id. One job can assigned to multiple user with job_id in job_assigned.

Now i want all job records with last inserted status in job_status_detail.

I use following query

SELECT * FROM `jobs`  as `t` 
 LEFT OUTER JOIN `job_assigned` as `jobDetail` ON (`jobDetail`.`job_id`=`t`.`id`) 
 LEFT OUTER JOIN `job_status_detail` as `job` ON (`job`.`job_id`=`t`.`id`) WHERE jobDetail.assign_to=1
 Order by job.id

but it gives me whole data. if one job has three entry in job_status_detail, it gives me 3 entries.Not last inserted entry.

Result should like one job with one job status(last inserted).

Answer 1

You have to put group by tag

SELECT * FROM `jobs`  as `t` 
 LEFT OUTER JOIN `job_assigned` as `jobDetail` ON (`jobDetail`.`job_id`=`t`.`id`) 
 LEFT OUTER JOIN `job_status_detail` as `job` ON (`job`.`job_id`=`t`.`id`) 
GROUP BY job.job_status Order by job.id