[SOLVED] Selecting all rows from tableA and join for each row the matching row from tableB

Issue

I have two tables: personal and persoLog. One represents personal and the other represents the log of personal events. The persoLog has a personal_log pointing to the personal table. I use join for this but either it is not working as expected or I know it wrong how to use it. Here are the tables:

ID  NAME    SECTION
1   joe     driver
2   george  technic

ID  PERSONAL_ID EVENT   DATE
1   1           idle    November, 26 2014 12:28:52+0000
2   1           drive   November, 26 2014 12:28:52+0000
3   1           idle    November, 26 2014 12:28:52+0000
4   2           idle    November, 26 2014 12:28:52+0000
5   2           idle    November, 26 2014 12:28:52+0000

So I want to list the personal and their latest log like here:

ID  NAME    SECTION EVENT   DATE
1   joe     driver  idle    November, 26 2014 12:28:52+0000
2   george  technic idle    November, 26 2014 12:28:52+0000

I use this query

SELECT p.*, pl.event, pl.date 
  FROM personal p
RIGHT JOIN persoLog pl 
  ON p.id = pl.personal_id

I expected to return all personal with the only matching log but I got this instead:

ID  NAME  SECTION  EVENT  DATE
1   joe    driver  idle   November, 26 2014 12:40:24+0000
1   joe    driver  drive  November, 26 2014 12:40:24+0000
1   joe    driver  idle   November, 26 2014 12:40:24+0000
2   george technic idle   November, 26 2014 12:40:24+0000
2   george technic idle   November, 26 2014 12:40:24+0000 

What am I doing wrong?

That is the SQLfiddle link for my question:
http://sqlfiddle.com/#!2/da315a/2

Solution

Try this:

SELECT p.*,
pl.event, pl.date 
FROM personal p
JOIN (
     SELECT personal_id,max(date) as LastDate from persoLog
     GROUP BY personal_id) x on p.id=x.personal_id
JOIN persoLog pl ON p.id = pl.personal_id 
AND pl.date=x.lastDate

However, in your example data, all of the dates are the same in the log table. Fix that and the above should work

Answered By – Sparky

Answer Checked By – David Goodson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.