JOIN multiple tables (1 Viewer)

[bilakos93]

Hello

I have 3 tables
Table1: FirstName, LastName, ID
Table2: ID, diagnosis (dropdown list), dstartdate, denddate
Table3: ID, admissiondate, dischargedate

I want to create a query that will display the IDs and the dstartdate and denddate from table 2 and admissiondate and dischargedate from table 3. If a person has multiple diagnoses or admissions, I want it to be displayed in separate rows. I want to show the records that apply to the following
(denddsate>=startdate OR dstartdate<=enddate) AND (dischargedate>=startdate OR dischargedate<=enddate)
Startdate and enddate is when the semester starts and ends and will be entered by the userx when opening the query.
Is there a way I can contain everything in one query?
I guess it will be a case of using INNER JOIN twice?


PS I eventually want to create a crosstab query where I will count how many days each ID had each diagnosis (table 2) and was inpatient (table 3)

Thanks

 

[theDBguy]

Hi. Welcome to AWF!

In cases where there may not be any matching diagnosis or admissions for the patient record, you may want to use an OUTER JOIN instead.

 

[plog]

(denddsate>=startdate OR dstartdate<=enddate) AND (dischargedate>=startdate OR dischargedate<=enddate)

If that logic is correct then that's your ON criteria for the JOIN:

...FROM t2 INNER JOIN t3 ON (denddsate>=startdate OR dstartdate<=enddate) AND (dischargedate>=startdate OR dischargedate<=enddate)...

I think it could be simplier though:

if diagnosis start is on or before discharge and diagnosis end is on or after admission

 

[bilakos93]

If that logic is correct then that's your ON criteria for the JOIN:

...FROM t2 INNER JOIN t3 ON (denddsate>=startdate OR dstartdate<=enddate) AND (dischargedate>=startdate OR dischargedate<=enddate)...

I think it could be simplier though:

if diagnosis start is on or before discharge and diagnosis end is on or after admission

thank you
can you be more descriptive though? I'm still too amateur I'm afraid

Is it
SELECT FirstName, LastName, ID, diagnosis, dstartdate, denddate, admissiondate, dischargedate, [startdate] AS expr1, [enddate] AS expr2
FROM Table1 INNER JOIN Table2 ON table1.ID=table2.ID

This is how far I've come

 

[plog]

The parts of queries are called clauses. Each clause starts with a keyword (SELECT, FROM, WHERE, GROUP BY, ORDER BY, etc.). You need to change the FROM and INNER JOIN clauses to ones that resemble mine but make sense with your specific tables and fields.

Look at the SQL snippet I posted, copy it into yours and change the fields/tables to the ones of your database.

 

[mike60smart]

thank you
can you be more descriptive though? I'm still too amateur I'm afraid

Is it
SELECT FirstName, LastName, ID, diagnosis, dstartdate, denddate, admissiondate, dischargedate, [startdate] AS expr1, [enddate] AS expr2
FROM Table1 INNER JOIN Table2 ON table1.ID=table2.ID

This is how far I've come

Are you able to upload a copy of the database with no confidential data?

 

[Pat Hartman]

Table1: FirstName, LastName, ID
Table2: ID, diagnosis (dropdown list), dstartdate, denddate
Table3: ID, admissiondate, dischargedate

In the tables you have described there is no way to join any table to any other since there are no relationships.

Every table needs a primary key and you are only confusing yourself if you name the PK of EVERY table with the same name. Use a little common sense.
tbl1, PK = PatentID
tbl2, PK = PatientDiagnosisID
tbl3, PK = PatientDiagnosisAdmissionID
Assuming the relationship is hierarchical

Then tbl2 needs an FK to link to tbl1, name it PatientID to match the PK it joins to and tbl3 needs an FK to link to tbl2 which you should name PatientIDiagnosisID.

You technically need a fourth table that you can name tblDiagnosis or perhaps tblICD10Codes.. tbl2 then also has a FK to tblICD10Codes