|
|
OK, so here is my problem now. I took Jane's suggestion and built a custom multi-table query to build the report data that I need. I have the search page setup so that a single value is entered, and then it is used as a key value to get data from three tables total. The query is running seemingly fine, except that I suspect I am missing something in it to clean up the output results.
My issue though now lies in that one of the tables will almost certainly always have more than one row that matches the selection criteria. So what is happening is that I am getting multiple full data results instead of what I am looking for. Let me see if I can explain this a little better.
Here is the query I am using:
select `inventory`.`Intake_ID`,
`Cat_Name` AS "Cat's Name",
concat((YEAR(curdate())-YEAR(Date_Of_Birth)), ' Year(s),', (MONTH(curdate())-MONTH(Date_Of_Birth)), ' Month(s)') AS "Estimated Current Age",
concat(`Breed`, ' ', `Color`, ' ', `Markings`) AS "Breed/Color/Markings",
`Intake_Source` AS "Source of Intake",
`Date_Of_Intake` AS "Date of Intake",
`Altered` AS "Spayed or Neutered",
concat(`Adopter_First_Name`, ' ', `Adopter_Last_Name`) AS "Adopted To",
concat(`Adopter_Address`, ' ', `Adopter_Address_2`) AS "Address",
`Adopter_City` AS "City",
`Adopter_State` AS "State",
`Adopter_Zip_Code` AS "Zip Code",
`Adopter_Home_Phone` AS "Home Phone",
`Adopter_Alt_Phone` AS "Alternate Phone",
`Adopter_Email` AS "Email Address",
`Adoption_Date` AS "Date of Adoption",
`Adoption_Location` AS "Adoption Location",
`Adoption_Fee` AS "Adoption Fee",
`Adoption_Payment_Method` AS "Paid By",
`medical_records`.`Date_Of_Treatment` AS "Treatment Date(s)",
`medical_records`.`Treatment` AS "Type of Treatment",
`medical_records`.`Medication` AS "Medication Used",
`medical_records`.`Description` AS "Description of Treatment",
`medical_records`.`Clinic` AS "Clinic",
`inventory`.`Foster_Name` AS "Fostered By",
`foster_homes`.`Contact_Info` AS "You Can Contact The Foster At"
From `adoption_records`, `medical_records`, `inventory`, `foster_homes`
WHERE `adoption_records`.`Intake_ID` = `inventory`.`Intake_ID`
AND `medical_records`.`Intake_ID` = `inventory`.`Intake_ID`
AND `inventory`.`Foster_Name` = `foster_homes`.`Foster_Name`
What I am wanting is a nice formatted result that shows only one instance of all of the data that is not from the `medical_records` table followed by each of the individula results from the `medical_records` table. What I am getting is each record from the `medical_records` table in a separate full data record that includes all of the other results. Which then leads to another problem with my redesigned Printer-Friendly page, but I'll worry about that once I get this other issue figured out.
Is the way I am trying to format the result data even possible or are my users just going to have to run multiple queries from other pages to generate what I am trying to get?
Thank for the help.
--Shawn
|