Joining two tables and returning fields from MAX(id) row

[Landslyde]

Well, this makes the 8th forum today   I guess this is not the normal query to be asking help on, but here we go.

 

I have these two tables: attendees & history. Each attendee has a single entry in the attendee table, while all their payments to the therapist are stored in the history table. What I'm trying to do is retrieve the fields from the last payment the attendee made in the history table using MAX(historyid) and attendeeid, while getting their name from the attendee table. Here are the table schemas:

TABLE `attendees` (
  `attendeeid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fname` varchar(20) NOT NULL,
  `lname` varchar(20) NOT NULL,
  `dojid` varchar(10) NOT NULL,
  `address1` varchar(25) NOT NULL,
  `address2` varchar(25) NOT NULL,
  `city` varchar(20) NOT NULL,
  `state` char(2) NOT NULL,
  `zipcode` varchar(5) NOT NULL,
  `phonenumber` varchar(15) NOT NULL,
  `paroleofficer` varchar(20) NOT NULL,
  `childrest` char(1) NOT NULL,
  `casedetails` varchar(255) NOT NULL,
  `disdate` date NOT NULL,
  `memberid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`attendeeid`),
  KEY `memberid` (`memberid`),
  CONSTRAINT `attendees_ibfk_2` FOREIGN KEY (`memberid`) REFERENCES `members` (`memberid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


TABLE `history` (
  `historyid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `amount` float NOT NULL,
  `subsidy` char(1) NOT NULL,
  `last_payment` date NOT NULL,
  `amount_paid` float NOT NULL,
  `balance` float NOT NULL,
  `attendeeid` int(10) unsigned NOT NULL,
  `memberid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`historyid`),
  KEY `attendeeid` (`attendeeid`),
  CONSTRAINT `history_ibfk_2` FOREIGN KEY (`attendeeid`) REFERENCES `attendees` (`attendeeid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The query I'm using sure 'nuff returns MAX(historyid), but the fields are from the first-ever entered row in the History table instead of the MAX(historyid) row.

select * 
from (select a.attendeeid,max(h.historyid),fname,lname,h.last_payment,h.balance
      FROM history AS h
      INNER JOIN attendees AS a
      ON a.attendeeid = h.attendeeid
      group by a.attendeeid) as maxHistoryPerAttendee
      where attendeeid = 29

I'm new to MySQL and really don't even understand the query (given to me by someone in another forum). Would someone please help me out of the woods here? I've been at this one for a day and a half with nothing to show for it.

[kicken]

If you want the most recent payment, you don't want MAX(historyid), you want MAX(last_payment). You shouldn't assume that a higher ID value means newer data. Always test against an actual date value that will tell you what data is newer.

 

Once you get MAX(last_payment) then you join back to the history table using that value to find the row that corresponds to that date. For example:

select 
	a.fname
	, a.lname
    , a.address1
    , a.city
    , a.state
    , h.historyid
    , h.last_payment
    , h.amount
from attendees a 
INNER JOIN (SELECT attendeeid, MAX(last_payment) as last_payment FROM history GROUP BY attendeeid) maxHistory ON maxHistory.attendeeid=a.attendeeid
INNER JOIN history h ON a.attendeeid = h.attendeeid AND h.last_payment=maxHistory.last_payment
WHERE
    a.attendeeid=2

See the sample sql fiddle to see it in action.

 

Note that unless last_payment is unique, this could still get you multiple rows of data. If there is potential for multiple history entries on the same date, you'll need to decide how to handle that. Either choose another date/time field that will be unique or combine the last date with something else.

[Landslyde]

kicken, yes, each attendee will have but one entry per week, so, with their attendeeid's, last_payment will be unique for each attendee. I'm flipping back and forth, studying your code. I really appreciate your help, kicken. I never come here trying to get anyone to write my code for me; I only need small examples like you provided to steer me in the right direction. That said, I do have a question about part of the query.

 

Will you kindly explain this to me, especially the use of maxHistory. As I see it, you're setting the proceeding SELECT stmt to maxHistory, right? Sorry, just trying to wrap my mind around it to where I understand as it's suppose to be. A HUGE thanks to you!

INNER JOIN (SELECT attendeeid, MAX(last_payment) as last_payment FROM history GROUP BY attendeeid) maxHistory ON maxHistory.attendeeid=a.attendee

Edited March 14, 2015 by Landslyde

[kicken]

maxHistory is just a table alias for the sub-query. It provides a way to reference the temporary table created by the sub query for the join condition.

 

It's just like where I did FROM attendees a to alias the attendees table to a. Rather than a simple table name though you have a sub-query to define the table.

[Landslyde]

For me, that query's really involved, not just a simple SELECT going on there. Am thankful to you for the example to learn by...and your follow-up explanation. Goes a long way with me. Have a good one, kicken.