0

Novice user.

I have two tables:

personnel(personnel_id, employee_id, f_name, l_name, initials, department, role), track(track_id, personnel_id, status, time_in, time_out, location)

"A person can be tracked many times"

To attempt to render just the required parts of the data set, I am using the query: 

SELECT 
    personnel.personnel_id, 
    personnel.employee_id, 
    personnel.l_name,  
    personnel.initials, 
    track.status, 
    track.time_in, 
    track.time_out 
FROM
    personnel, track
WHERE
    personnel.personnel_id = track.personnel_id
ORDER BY
    l_name

The issue here is that it returns every instance of the foreign key's appearance in the track table when all I need it to do is display the personnel details with the most recently inserted track data. It also returns the same personnel data multiple times per occurrence of the foreign key. I know the problem lies in the query and I have ventured into JOINS but I'm still in beginner land.

olli
  • 7
  • 1
NickC
  • 21
  • 1
  • 8

2 Answers2

0
SELECT
  p.personnel_id, 
  p.employee_id, 
  p.l_name,  
  p.initials, 
  outer_track.status, 
  outer_track.time_in, 
  outer_track.time_out 
FROM Personnel P, Track OuterTrack 
where P.personnel_id = OuterTrack.personnel_id and
OuterTrack.id = 
    (SELECT FIRST t.id from InnerTrack 
     order by InnerTrack.time_in desc 
     group by InnerTrack.personnel_id)
ORDER BY
    Personnel.l_name
Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
-1

Your question appears to be somewhat similar to another answered question that might be useful: MySQL “Group By” and “Order By”

The solution is to first order the query according to the most recently inserted track data (I'm assuming this will have the highest track_id for each personnel) then to group those results by unique the personnel id.

First, create a temporary table with only the track_id of the most recently inserted track for each personnel:

CREATE TEMPORARY TABLE 
    recent_track 
SELECT 
    recent_track_id 
FROM (
    SELECT
        personnel_id, 
        max(track_id) as recent_track_id
    FROM
        track
    GROUP BY
        personnel_id) as tmp_tab;

Then using the most recent track_id for each personnel, call all of the rest of the data you need to display:

SELECT 
    p.personnel_id, 
    p.employee_id, 
    p.l_name,  
    p.initials, 
    t.status, 
    t.time_in, 
    t.time_out 
FROM
    personnel AS p, track AS t
WHERE
    p.personnel_id = t.personnel_id 
    AND  
        t.track_id IN (SELECT recent_track_id FROM recent_track);
Community
  • 1
  • 1
amills61
  • 1
  • 1
  • This is not valid. `GROUP BY` should only be used with aggregate functions, there's no guarantee of which rows will be returned when you select columns. See http://stackoverflow.com/questions/1591909/group-by-behavior-when-no-aggregate-functions-are-present-in-the-select-clause – Barmar Aug 26 '12 at 04:16
  • I see your point - I revised the answer to remove the ambiguity from using `GROUP BY`. – amills61 Aug 26 '12 at 08:19
  • Many thanks for your replies, I'm going to experiment with all answers and see what comes of it. Regards. – NickC Aug 26 '12 at 14:31
  • Any luck sorting out your question? – amills61 Sep 10 '12 at 05:54