Re: limiting join results

Elaine Lindelef wrote:
> I am doing a query with a 3-way join. The join and select are working
> fine. However, what I want is to select only the row with the smallest
> timediff for each distinct t1.date.

First of all, you are using left joins. I don't think that's what you
want. A left join says to include all matching rows from t1, even if
there's no matching row in t2; it makes a fake t2 with all nulls if
necessary to match the t1 with. (Not a terribly good explanation. If you
need better, do a web search or I can try again.) Likewise for t2 and
t3. But you are discarding those with the t3.date comparison, because
"t3.date < t1.date" will always be false if t3.date is null, as it would
be for the extra stuff from the left join. You probably want an inner
join (the normal kind), which is a lot less expensive also.

And the way I typically say the smallest/largest/whateverest something
is with a not exists clause. Something that says "there is no record
with a smaller timediff than this one and matching it otherwise."

I'll use a simpler example (only one table in the not exists) to
demonstrate that with. Let's just say I'm interested in finding the
oldest employee in each department of some business.

create table department (
department_id serial primary key,
name varchar(50) not null
);

create table employee (
employee_id serial primary key,
department_id integer references department not null,
name varchar(50) not null,
when_born date
);

select department.name as department_name,
employee.name as employee_name,
when_born
from employee natural join department
where not exists
(select 'x'
from employee as older_employee
where employee.department_id
= older_employee.department_id
and older_employee.when_born < employee.when_born);

In other words, show me the department name, employee name, and birth
date of every employee for whom there is no older employee in the same
department.

Does that help?

Scott