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.