sql - SELECT fields from one table with aggregates from related table -

here simplified description of 2 tables:

create table jobs(id primary key, description); create table dates(id primary key, job references jobs(id), date); 

there may 1 or more dates per job.

i create query generates following (in pidgin):

jobs.id, jobs.description, min(dates.date) start, max(dates.date) finish 

i have tried this:

select id, description,       (select min(date) start  dates d d.job=j.id),       (select max(date) finish dates d d.job=j.id) jobs j; 

which works, looks inefficient.

i have tried inner join, can’t see how join jobs suitable aggregate query on dates.

can suggest clean efficient way this?

while retrieving rows: aggregate first, join later:

select id, j.description, d.start, d.finish   jobs j left   join (    select job id, min(date) start, max(date) finish       dates     group  job    ) d using (id); 

related:

• sql: how save order in sql query?

about join .. using

it's not "different type of join". using (col) standard sql (!) syntax shortcut on a.col = b.col. more precisely, quoting manual:

the using clause shorthand allows take advantage of specific situation both sides of join use same name joining column(s). takes comma-separated list of shared column names , forms join condition includes equality comparison each one. example, joining t1 , t2 using (a, b) produces join condition on *t1*.a = *t2*.a , *t1*.b = *t2*.b.

furthermore, output of join using suppresses redundant columns: there no need print both of matched columns, since must have equal values. while join on produces columns t1 followed columns t2, join using produces 1 output column each of listed column pairs (in listed order), followed remaining columns t1, followed remaining columns t2.

it's particularly convenient can write select * ... , joining columns listed once.