[RESOLVED] joining a parent record with the LATEST child record

sfullman

I know this probably flys in the face of your basic GROUP BY functionality but I am beginning to experiment with views and subqueries and would like to have this be possible:

you have a parent table with these fields:

personid
firstname
lastname
email
etc.

you have a child table of subscriptions (I will constrain so none of the dates will overlap):
subid
personid (foreign key)
startdate
enddate
status
cost
paid (yes/no we'll say)

one person may have zero, one or many subscriptions.

What I'd like to do is create a view comprising the person info and their LATEST subscription, if one exists (else NULL for all those columns). Can this be done? Many thanks in advance for your assistance.

Samuel

bradgrafelman

Here's my stab at it:

SELECT p.firstname, p.lastname, s.subid, s.startdate, s.enddate
FROM people p JOIN subscriptions s ON p.personid = s.personid
GROUP BY s.personid
ORDER BY s.subid DESC
LIMIT 1

Untested, so the GROUP BY may not work as expected.

sfullman

group by does NOT work as expected, and limit 1 doesn't either - that's only going to return the first record in the query.

there is a way to do it

create table _people(
id int(3),
firstname char(30),
lastname char(30)
);

create table _subs(
id int(3),
personid id(3),
subdate date,
status char(15)
);

create view _maxsub as
select
b.peopleid, max(b.subdate) as subdate
from _people a, _subs b
WHERE a.id=b.peopleid;

select
a.id as personaid, a.firstname, a.lastname, c.subdate, c.status
from _people a LEFT JOIN _maxsub b ON a.id=b.peopleid
LEFT JOIN _subs c ON b.subdate=c.subdate
/* if there was any chance of duplicate subscription dates we'd need to also "group by a.ID" I believe */

the problem is that the join seems very weak and as I noted in comments, if there were two identical subdates, we'd have double records, and if we grouped for compaction, the record would still be ambiguous - but then again, if there were two identical max subdates, the data itself WOULD be ambiguous.

I was just wondering if there's a more elegant way to do this via subqueries or the like without creating a intermediate view

bradgrafelman

sfullman;10948175 wrote:
limit 1 doesn't either - that's only going to return the first record in the query

The first record in the result set, yes. Isn't that what you wanted? You wanted just the newest subscription only? I had assumed the subscription with the highest ID # was the most recent (a.k.a. "newest") which is why I used the ORDER BY statement.

sfullman

right but what I want(ed) is:

the MOST CURRENT SUBSCRIPTION for EACH person (if present, else NULL for those columns)

group by is not an acceptable solution because when you group you can no longer control or view the attributes of any one record in the group. And, in this case I need ONE record from the group.

Look at the query I did. It works, but seems cumbersome, and I'd like to do the equivalent without an intermediate view being used

bradgrafelman

How do you define which subscription is the 'most current' ? The one with the greatest "enddate" ?

sfullman

I kind of left off the enddate and went with the startdate, but yes the greatest "startdate" - in practice these subs might be ranges of 1/1/2007 - 12/31/2007, then 1/1/2008 - 12/31/2008, etc. etc.

like I said, the series of tables and the one view I created does work (though I had some sql parse errors so don't take those tables literally 🙂 but I'd like to know how to do it without a intermediate view in the query - just by one query

sfullman

Hi, I'm doing this for now with the intermediate view but I'm wondering if anyone can answer with regard to elminating the view and doing this with one single query - a list of members and their MOST RECENT startdate based on the highest value of the field

sfullman

I still think there is a more elegant way to do this BUT a subquery did it. here is the code

SELECT
a.ID, a.Active, a.un_username AS UserName, a.Relationship, a.Category, a.FirstName, a.MiddleName, a.LastName, a.Gender, a.Race, a.BirthDate, a.SocSecurityNumber, a.Address, a.City, a.State, a.Zip, a.Phone, a.Fax, a.CreateDate,
COUNT(DISTINCT b.Objects_ID) HomeCount,
c.EventDate AS LastCBC,
d.Status,
d.PerformingAgency,
d.PerformingAgencyPhone,
d.ReferenceNumber,
d.Notes AS CBCNotes
FROM
gf_subcontractors a LEFT JOIN gf_objects b ON a.ID=b.Subcontractors_ID AND b.ParentObject='gf_fosterhomes'
LEFT JOIN (
	SELECT Objects_ID AS _max_sub_CBC_ID_, MAX(EventDate) AS EventDate
	FROM gf_objects
	WHERE 
	Relationship = 'Criminal Background Check' 
	AND ParentObject='gf_subcontractors'
	GROUP BY Objects_ID
) c ON a.ID=c._max_sub_CBC_ID_ 
LEFT JOIN gf_objects d ON c.EventDate=d.EventDate AND d.Objects_ID=a.ID
GROUP BY a.ID

not only that but it appears to be a very fast query - the table names aren't super obvious but you'll get the concept if you study it. hth someone