user3572565 user3572565 - 7 months ago 34
SQL Question

Selecting head records with only the first occurring line record

I have 2 tables. SHead & SLine


id - title - origin - Status - datetime


id - sid - datetime - body

I want to select all records from SHead where origin = 4. I want only the first recorded row from SLine though.

So if I have one row in SHead for origin=4 and two related rows in SLine, I only want to return one row. Is this possible?


Here is a typical way:

select h.*, l.*
from shead h join
     sline l
     on l.sid =
where s.original = 4 and
      l.datetime = (select min(l2.datetime) from sline l2 where l2.sid = l.sid);