user3572565 user3572565 - 1 month ago 7
SQL Question

Selecting head records with only the first occurring line record

I have 2 tables. SHead & SLine

SHead

id - title - origin - Status - datetime


SLine

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?

Answer

Here is a typical way:

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