C
CuriousMark
I imported a table into my database and normalized it into several tables.
Now I can't figure out how to reconstruct the original table with queries.
The data relate to procedures done on patients. At a given "encounter" a
patient can have one or more procedures done. So the original table looked
like this, with a Yes/null for each of the three procedures (if the procedure
was not done during the encounter, the cell was left blank):
Date LastName FirstName ProcA ProcB ProcC
1/1/08 Smith John Yes
1/2/08 Jones Steven Yes
1/3/08 Adams John Yes Yes
I normalized the data into four tables like this:
tblPatients
PatientID (pk)
LastName
FirstName
tblEncounters
EncounterID (pk)
PatientID (fk from tblPatients)
EncounterDate
tblEncounterProcedure
EncounterProcedureID (pk)
EncounterID (fk from tblEncounters)
ProcedureID (fk from tblProcedures)
tblProcedures
ProcedureID (pk)
ProcedureName (ProcedureA, ProcedureB or ProcedureC)
How do I write a query to "recreate" the original table, with columns for
each of the three procedures with a "yes" or null value in them? The queries
that I try either give me a cartesian product, or won't include an encounter
if any of the three procedures was not done at that encounter.
How do I get there from the normalized tables? And, should I have left well
enough alone?
Now I can't figure out how to reconstruct the original table with queries.
The data relate to procedures done on patients. At a given "encounter" a
patient can have one or more procedures done. So the original table looked
like this, with a Yes/null for each of the three procedures (if the procedure
was not done during the encounter, the cell was left blank):
Date LastName FirstName ProcA ProcB ProcC
1/1/08 Smith John Yes
1/2/08 Jones Steven Yes
1/3/08 Adams John Yes Yes
I normalized the data into four tables like this:
tblPatients
PatientID (pk)
LastName
FirstName
tblEncounters
EncounterID (pk)
PatientID (fk from tblPatients)
EncounterDate
tblEncounterProcedure
EncounterProcedureID (pk)
EncounterID (fk from tblEncounters)
ProcedureID (fk from tblProcedures)
tblProcedures
ProcedureID (pk)
ProcedureName (ProcedureA, ProcedureB or ProcedureC)
How do I write a query to "recreate" the original table, with columns for
each of the three procedures with a "yes" or null value in them? The queries
that I try either give me a cartesian product, or won't include an encounter
if any of the three procedures was not done at that encounter.
How do I get there from the normalized tables? And, should I have left well
enough alone?