DISTINCT with multiple tables and INNER JOIN ?

My query works OK this way, but I need every "kohde_name" only once
(preferred the earliest "hav_date").
I've tried with "DISTINCT kohde_name" in many ways, but always end up with
syntax error...:(
Is there any solution with DISTINCT, or do I need anotjer solution?

my (simplified) query:
SELECT henk_name,henk_number, kohde_name
,kohde_txt,havainto_id,hav_kohdetxt, hav_date
FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id)
INNER JOIN havainto ON hja_havainto_id=havainto_id)
INNER JOIN kohde ON hav_kohde_id=kohde_id
WHERE ...

Tommi.P.

Re: DISTINCT with multiple tables and INNER JOIN ?

.. wrote:
> My query works OK this way, but I need every "kohde_name" only once
> (preferred the earliest "hav_date").
> I've tried with "DISTINCT kohde_name" in many ways, but always end up with
> syntax error...:(
> Is there any solution with DISTINCT, or do I need anotjer solution?
>
> my (simplified) query:
> SELECT henk_name,henk_number, kohde_name
> ,kohde_txt,havainto_id,hav_kohdetxt, hav_date
> FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id)
> INNER JOIN havainto ON hja_havainto_id=havainto_id)
> INNER JOIN kohde ON hav_kohde_id=kohde_id
> WHERE ...
>
> Tommi.P.

OK, I'm guessing a bit at your table structure here - not helped by my
decidedly limited knowledge of Finnish, but see if you can adapt this
to your needs (maybe as a derived table onto which everything else is
joined:

SELECT hto1 . *
FROM havainto hto1
LEFT JOIN havainto hto2 ON hto1.hav_kohdetxt = hto2.hav_kohdetxt
AND hto1.havainto_id <> hto2.havainto_id
AND hto1.hav_date < hto2.hav_date
WHERE hto2.hav_date IS NULL