Take the 2-minute tour ×
SELECT * FROM assignments 
LEFT JOIN workers ON workers.id = assignments.id 
LEFT JOIN positions ON positions.id = assignments.position 
LEFT JOIN position_descriptions ON position_descriptions.id = positions.id 
LEFT JOIN employers ON employers.id = positions.employerid 
LEFT JOIN additonal_data ON additonal_data.id = assignments.id 
**LEFT JOIN dates ON dates.id = assignments.id**
WHERE assignments.status = 3
**AND dates.work_length= 3** 
**GROUP BY assignments.id**
ORDER BY assignments.id DESC

I only have 2 users in original query. Now when I added the dates table it started multiplying number of users by how many dates was there with dates.work_length = 3 so I grouped by assignments.id and it works good.

But now in table dates there are work_length = 3 twice (first on jan.01.2011, second on jul.01.2011) and with this query it returns the first date; I need it to return the latest date jul.01.2011.

share|improve this question
Yes but you are using left join which will return unmatched records try doing this with 'INNER JOIN' –  user466764 Aug 31 '11 at 15:18
You need the latest date as a column in your result set ? What is the structure of the dates table? If this is the case, you can obtain the information you want using an aggregate function such as MAX . –  wisefish Oct 1 '11 at 16:28
add comment

Your Answer



Browse other questions tagged or ask your own question.