Join 2 tables with known variable sorting by max(date)

Question

I have 2 tables transfers and equipment where there could be multiple transfers for each piece of equipment. I'm looking to join these 2 tables with a project id and return the latest transfer for each piece of equipment for a specific project.

transfers table:
tid, pid, eid, date

equipment table:
eid, ename

In my function I'm sending the variable $pid to sort out just the equipment transfers for a specific project id that is stored with the equipment id in the transfers table.

Answer 1

I think your requirements may still be a little unclear to me, but to return only the latest "Transfer" & "Equipment" rows for a specific PID, perform the following:

SELECT t.tid, t.pid, t.eid, e.ename, t.date
  FROM transfers t
  JOIN equipment e ON t.eid = e.eid
 WHERE t.pid = ?
   AND t.date = (SELECT MAX(t2.date) FROM transfers t2 WHERE t2.pid = ? )

Let me know if this does what you're hoping it will do. If not, give me a little bit more information on the rows that would be in each of the tables.

Hope this helps,

john...

Answer 2

You want the groupwise maximum:

SELECT *
FROM   transfers NATURAL JOIN (
  SELECT   pid, eid, MAX(date) date
  FROM     transfers
  WHERE    pid = ?
  GROUP BY eid
) t JOIN equipment USING (eid)