Help with complex select query please..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Help with complex select query please..

Post 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
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Re: Help with complex select query please..

Post 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')
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Play around with Delorians first example, exchanging the last couple of or's to and's.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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
Post Reply