I have this query:

SELECT e.encounter_nr, e.encounter_class_nr, e.official_receipt_nr, p.pid, p.name_last, p.name_first, p.date_birth, p.addr_zip, p.sex,p.blood_group FROM care_encounter AS e LEFT JOIN care_person AS p ON e.pid=p.pid WHERE (p.name_last LIKE 'Reyes%') AND e.is_discharged IN (0) AND e.is_discharged IS NOT NULL AND e.status NOT IN ('deleted','hidden','inactive','void')

However the output display duplicate values. The unique value there is p.pid

so I tried ( Select DISTINCT(p.pid), e.encounter_nr, e.encounter_class_nr, e.official_receipt_nr, p.name_last, p.name_first, p.date_birth, p.addr_zip, p.sex,p.blood_group FROM care_encounter ....)

but result is the same...still returns duplicate values...

The DISTINCT keyword ensures that there are no duplicate rows meaning that no two rows will have the exact same values in all of the columns.

What you seem to want is a single row for each p.pid, right? In order to achieve that, you need to group the rows by p.pid using the GROUP BY clause. At that point, the real question arises: which values do you want to return for the different columns and a single p.pid value? Remember, SQL is a set-oriented language and sets are not sorted. So there is no "first" row or so. You'll have to choose the minimum value, maximum or some other value. It is up to you to answer that question.

I have resolve it.Thanx..Here is my query:
SELECT min(e.encounter_nr)as encounter_nr , min(e.encounter_class_nr), min(e.official_receipt_nr), p.pid, max(p.name_last), min(p.name_first), min(p.date_birth), min(p.addr_zip), min(p.sex), min(p.blood_group) FROM care_encounter AS e LEFT JOIN care_person AS p ON e.pid=p.pid WHERE (p.name_last LIKE 'Reyes%') AND e.is_discharged IN (0) AND e.is_discharged IS NOT NULL AND e.status NOT IN ('deleted','hidden','inactive','void') GROUP BY p.pid

In the query which adodb21 reached, each column's values were selected from (possibly)different rows.
That might satisfy adodb21's requirement.

But, if you want to take all values from a specific single row for each pid,
Serge Rielau provided some examples of the topic in "SQL on Fire! Part 1" under the title "Retrieving MAXimun row".

http://www.iiug.org/waiug/present/Fo...rge_Rielau.ppt

lotsa crap in that powerpoint presentation, no offence to you tonkuma

"groupwise maximum" is a common problem, here are 11 solutions --> Biggest Country of each Continent (groupwise maximum)

by the way, adodb21, you want an INNER JOIN, not a LEFT OUTER JOIN