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.

[strawberry]

.. 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