-1

I have two tables,

---Jobs---
id,name,.....

---Locations---
id,jobId,Location,date

now a job can have more then one location I want to join the job with it's latest location where jobs.id=location.jobId and date is greatest.

zavg
  • 10,351
  • 4
  • 44
  • 67
user3032474
  • 15
  • 4
  • 5
    Do you also have a question for us? (Hopefully something other than just 'do it for me') – nl-x Nov 25 '13 at 13:27
  • 1
    you already know you want to use a sql join. a quick google search provides 20 entries on page 1 on how to do joins exactly as you want. – Dave Nov 25 '13 at 13:28
  • It's not crystal clear, but I would guess his/her issue is how to only get the *latest* location, rather than the actual joining? – Shai Nov 25 '13 at 13:30
  • I looked in google , did not find anything that helped me The reason I'm asking is because I'm not sure about the syntext. Shai, Yep – user3032474 Nov 25 '13 at 13:38

2 Answers2

0

Try this:

select * from jobs inner join locations on jobs.id=locations.jobid order by date desc limit 1;
Mehdi
  • 661
  • 5
  • 17
  • Same I get this error #1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' – user3032474 Nov 25 '13 at 13:36
0

Could be:

SELECT * 
FROM Jobs J, Locations L
WHERE J.id=L.jobId
AND L.date = (
   SELECT MAX(date) 
   FROM Locations L2
   WHERE L2.jobId=L.jobId
   GROUP BY L2.jobId)

Explanation:

In the nested Query I am selecting the maximum date for each Job.

In the external Query i am joining the two tables and fetching only the rows where the date is equal to the MAX(date) i found on the nested query and the Job is the same. This is accomplished by the condition L2.jobId=L.jobId

Alberto Fontana
  • 928
  • 1
  • 14
  • 35
  • I get this error #1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' – user3032474 Nov 25 '13 at 13:35
  • Yeah, i probably forgot to use Group By. Query updated. BTW that error looks like your tables have different collations. One has utf8_unicode_ci, the other one has utf8_general_ci. Try to make them the same collation. Explanation here http://stackoverflow.com/questions/1241856/illegal-mix-of-collations-error-in-mysql – Alberto Fontana Nov 25 '13 at 13:38
  • Ya changed to collations and it worked, Thanks – user3032474 Nov 25 '13 at 13:46