Get first record from database in date range

[saadi06]

Hi,
I have a problem in making a query.I have two tables patient and encounter. Patient has one to many relation with the encounter table. The encounter table has a field encounter_date. I want to make a search query in which I enter the date ranges and the query fetches the encounter
between the date range and check whether the encounter is first encounter of the patient. For example a patient has two encounters on 2012-02-01 and 2012-06-07 and I search between 2012-01-29 to 2012-02-05.The search result should be displayed as the patients first encounter is on 2012-02-01 and it should not come in the date range for 2012-06-01 and 2012-06-10.I have made a query but it is having problem. Here is my query

SELECT  patient.origid          AS 'Patient Id',
          patient.firstname       AS 'First Name',
          patient.lastname        AS 'Last Name',
          patient.middlename      AS 'Middle Name',
          DATE_FORMAT(encounter.encounter_date,'%Y-%m-%d')        AS 'Encounter Date',
          FROM patient
          INNER JOIN encounter ON  encounter.origid = patient.origid 
          WHERE NOT  encounter.encounter_status='cancelled'
          AND encounter.encounter_date BETWEEN '2012-02-01' AND '2012-02-10'
          GROUP BY encounter.origid ORDER BY encounter.encounter_date ASC
          LIMIT 15

Now the problem with this query is that it does not take in account that is this first encounter of the user or not.I think I have to implement an inner query for this but cant think of it.
Can anyone help.
Thanks in advance.

[diafol]

SO you're not searching for a particular patient - you want ALL first encounters between 2 dates. That right?

If so, you may need to do a subquery instead of using ther encounter table as is.
THat may be something like:

...INNER JOIN (SELECT origid, MIN(encounter_date) FROM encounter WHERE encounter_date BETWEEN '...' AND '...' GROUP BY oridig) AS enc ON enc.origid ...

[saadi06]

Yes exactly.

[diafol]

As I don't have your table setups, I'm just typing out loud here. Maybe this will work?

SELECT  patient.origid          AS 'Patient Id',
      patient.firstname       AS 'First Name',
      patient.lastname        AS 'Last Name',
      patient.middlename      AS 'Middle Name',
      DATE_FORMAT(enc.ed,'%Y-%m-%d')   AS encdate,
      FROM patient
      INNER JOIN (SELECT origid, MIN(encounter_date) AS ed FROM encounter WHERE NOT  encounter_status='cancelled' AND encounter_date BETWEEN '2012-02-01' AND '2012-02-10' GROUP BY oridig) AS enc ON  enc.origid = patient.origid 
     ORDER BY enc.encounter_date ASC
      LIMIT 15