Page 1 of 1

Help with complex select query please..

Posted: Mon Dec 22, 2003 12:10 pm
by waskelton4
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..

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'
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

Re: Help with complex select query please..

Posted: Mon Dec 22, 2003 12:17 pm
by delorian
Maybe, I did not understand you very well (sorry), but you can try this:

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.DiagName='diag1' or sd.DiagName='diag2' or sd.DiagName='diag3')
Or

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.DiagName IN ('diag1', 'diag2', 'diag3')

Posted: Mon Dec 22, 2003 12:23 pm
by waskelton4
Thanks for the Reply!!

If i'm correct those querys will give me all of the instances that each diagnoses was diagnosed..

what i'm looking for is each visit that had ALL three of the diagnoses.

make sense?

Posted: Sat Dec 27, 2003 11:46 am
by JAM
Play around with Delorians first example, exchanging the last couple of or's to and's.

Posted: Sat Dec 27, 2003 10:31 pm
by McGruff
If I understood you corrrectly, I think this will do it. (Primary keys named as: visits = vid, state = sid, diagnosis = did):

Code: Select all

SELECT DISTINCT visits.їcol1], visits.їcol2], ..etc.. FROM visits
INNER JOIN state USING(vid)
INNER JOIN j_state_diagnosis* USING(sid)
INNER JOIN diagnosis USING(did)
WHERE diagnosis.їcolumnX] = $diagnosis1
OR diagnosis.їcolumnX] = $diagnosis2
OR diagnosis.їcolumnX] = $diagnosis3
*I find it helps to name join tables: "j_table1_table2". This identifies them as join tables and also shows what tables they are joining. Alphabetically sorted table lists put them all together in one nice block if you have dozens of tables to manage.

I wouldn't worry too much about speed & multiple table JOIN queries. It's not a big deal for a well-optimised query. Use EXPLAIN to examine how mysql is performing the JOIN and see notes in mysql manual. For example make sure columns used to join tables are indexed and of EXACTLY the same type.

Please note that I can't fully understand the logic behind the db structure you have chosen on the given info. I have a sneaking suspicion that this might not be the best way to model the system - but I don't really know. The key is to have it properly normalised and flexible enough to add in extra features later, if requested.

Some db tutorials:

http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf
http://www.devshed.com/Server_Side/MySQ ... page1.html