[TriLUG] Any SQL experts out there?

[Paul Damer]

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