While I'm not a newbie, occasionally I run across things I want done and don't even remember what keywords to use to search for information... :)
A simplified version of my schema:
visits
- id
- date
- person_id
hearing_test
- visit_id
- left_ear
- right_ear
vision_test
- visit_id
- left_eye
- right_eye
tongue_test /* tongue in cheek */
- visit_id
- left_tongue
- right_tongue
Given a visit_id, I want all past visits by date and test scores:
visit_id 21 32
date 7/20/05 5/7/08
left_ear 33 43
right_ear 26 34
left_eye 99 98
right_eye 58 55
left_tongue yuck bleck
right_tongue yum yummy
So basically the query will have to figure out the columns dynamically and/or I kinda remember there's some kind of "rotate this whole thing by 90 degrees" thing ... I have a list of all the columns down the left...
Anyway, any help or push in the right direction would be appreciated.
As I work with Rails and it's ORM I'm not very familiar with MySQL views... though I did a fair bit of work with views when I was working with C#... am I right in thinking this is a perfect candidate for a view?
jw
---
Johnathon Wright
Web Application Consultant
http://www.mustmodify.com