Help with complex select query please..
Posted: Mon Dec 22, 2003 12:10 pm
well.. at least it's complex for me..
I have a patient tracking system that logs patient visits and associates various info about that visit to the visit. (hope that made sense)
i have:
tblVisits: (contains data that generally won't change durring the visit)
tblState: a visit can have many states to it if doctors change diagnoses and/or medications..
tblDiagnoses: list of Diagnoses with IDs
link_StateDiags: used to associate Many diagnoses to Many States
What i need my query to do is tell me all of the visits that were all diagnosed with diag1, diag2 and diag3. (hope i explained this well enough)
the query i have thus far is..
This is giving me the list of states that contain the give DiagID (70)
i need the where clause to take the three Diag Names and not ID number.
i understand that with all the joins that it may take a super long time.. but i think if i can get teh sql to work first then i can break some of the joins off and make a php loop or two to get the data with new querys..
i hope this made some sort of sense.. it's confusing the crap out of me and getting too complicated for my sql knowledge and experience
TIA for any help and if you think you can help but need more info PLEASE let me know.
will
I have a patient tracking system that logs patient visits and associates various info about that visit to the visit. (hope that made sense)
i have:
tblVisits: (contains data that generally won't change durring the visit)
tblState: a visit can have many states to it if doctors change diagnoses and/or medications..
tblDiagnoses: list of Diagnoses with IDs
link_StateDiags: used to associate Many diagnoses to Many States
What i need my query to do is tell me all of the visits that were all diagnosed with diag1, diag2 and diag3. (hope i explained this well enough)
the query i have thus far is..
Code: Select all
SELECT v.autoVisitID, v.intPatID, v.dtAdmit, v.dtDischarge, s.autoStateID, sd.intDiagID
FROM `tblVisits` as v
left outer join tblState as s on v.autoVisitID = s.intVisitID
left outer join link_StateDiags as sd on s.autoStateID = sd.intStateID
where dtAdmit >= '2003-10-01 00:00:00' and sd.intDiagID = '70'i need the where clause to take the three Diag Names and not ID number.
i understand that with all the joins that it may take a super long time.. but i think if i can get teh sql to work first then i can break some of the joins off and make a php loop or two to get the data with new querys..
i hope this made some sort of sense.. it's confusing the crap out of me and getting too complicated for my sql knowledge and experience
TIA for any help and if you think you can help but need more info PLEASE let me know.
will