4 Table LEFT JOIN Questions
Posted: Thu Dec 03, 2009 5:31 pm
I'm trying to set a form up so that when a user wants to generate a report, it will show results of this query that is listed below. There are 4 tables:
=> Contacts - Holds patient information
=> MissedVisits - Missed Evaluations
=> Daily - Daily Evaluations
=> Evals - Full Evaluations
These tables all have tons of fields about the visits and are designed as seperate forms. Integrating them through a LEFT JOIN or INNER JOIN seems impractical, as it only seems practical to join one of the Evaluation tables to the Contacts table to link a patient to an evaluation.
My goal is to compare a set of criteria in a form to the tables and generate a report showing all records of events happening. It's easy if the user selects which evaluation they want to view, but I also want them to be able to select "All Form Types" and get a report showing all Evaluation types assigned to the $patID, or ID assigned to the patient, which is the same in all tables.
Is this query correct? If not, what should I use to query results from mulitple tables based on a date range (in the established format) and an ID?
NOTE: The SUBSTRING usage is to pull the month, day and year values from the TEXT formatted date MM/DD/YYYY and compare it to the exploded date set on a date field. This is what I use for date ranges.
=> Contacts - Holds patient information
=> MissedVisits - Missed Evaluations
=> Daily - Daily Evaluations
=> Evals - Full Evaluations
These tables all have tons of fields about the visits and are designed as seperate forms. Integrating them through a LEFT JOIN or INNER JOIN seems impractical, as it only seems practical to join one of the Evaluation tables to the Contacts table to link a patient to an evaluation.
My goal is to compare a set of criteria in a form to the tables and generate a report showing all records of events happening. It's easy if the user selects which evaluation they want to view, but I also want them to be able to select "All Form Types" and get a report showing all Evaluation types assigned to the $patID, or ID assigned to the patient, which is the same in all tables.
Is this query correct? If not, what should I use to query results from mulitple tables based on a date range (in the established format) and an ID?
NOTE: The SUBSTRING usage is to pull the month, day and year values from the TEXT formatted date MM/DD/YYYY and compare it to the exploded date set on a date field. This is what I use for date ranges.
Code: Select all
$query_selected = "SELECT * FROM contacts LEFT JOIN (missedVisit, daily, eval) ON (missedVisit.patient_id=contacts.contact_id AND eval.patient_id=contacts.contact_id AND daily.patient_id=contacts.contact_id) WHERE SUBSTRING(missedVisits.missed_aptDate,1,2) BETWEEN '$start_month' AND '$end_month' AND SUBSTRING(missedVisits.missed_aptDate,4,2) BETWEEN '$start_day' AND '$end_day' AND SUBSTRING(missedVisits.missed_aptDate,7,4) BETWEEN $start_year AND $end_year OR SUBSTRING(daily.daily_aptDate,1,2) BETWEEN '$start_month' AND '$end_month' AND SUBSTRING(daily.daily_aptDate,4,2) BETWEEN '$start_day' AND '$end_day' AND SUBSTRING(daily.daily_aptDate,7,4) BETWEEN $start_year AND $end_year OR SUBSTRING(eval.eval_aptDate,1,2) BETWEEN '$start_month' AND '$end_month' AND SUBSTRING(eval.eval_aptDate,4,2) BETWEEN '$start_day' AND '$end_day' AND SUBSTRING(eval.eval_aptDate,7,4) BETWEEN $start_year AND $end_year AND contacts.contact_id = ".$_GET['patID']."";