Andrew Perrin wrote:
OK, I'll simplify the data as much as possible.
Table "subjects": partid varchar(6) age smallint
partid | age ---------------------------------- W11111 | 15 W22222 | 25 ... W99999 | 34
Table "code_assignments": partid varchar(6) coder varchar(3) ca_date date tone_angry boolean
partid | coder | ca_date | tone_angry ------------------------------------------------------------- W11111 | ajp | 2004-11-10 | t W11111 | lmh | 2004-10-18 | f W11111 | kmk | 2005-1-15 | t W22222 | ajp | 2004-11-10 | t ... W99999 | lmh | 2005-01-15 | f W99999 | kmk | 2005-01-10 | t
I would like to return rows that look like:
partid | age | coder | ca_date | tone_angry ---------------------------------------------------------------------- W11111 | 15 | lmh | 2004-10-18 | f W22222 | 25 | ajp | 2004-11-10 | t W99999 | 34 | kmk | 2005-01-10 | t
such that data from "subjects" is joined with the *oldest* entry in code_assignments for the relevant partid.
Thanks- Andy
How about something along the lines of:
Select s.partid, s.age, c.coder, c.ca_date, c.tone_angry from subjects s, code_assignments c where c.partid = s.partid and c.ca_date = (select min(c2.ca_date) where c.partid = c2.partid)
-Paul
-- TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug TriLUG Organizational FAQ : http://trilug.org/faq/ TriLUG Member Services FAQ : http://members.trilug.org/services_faq/ TriLUG PGP Keyring : http://trilug.org/~chrish/trilug.asc
