Inspiring

質問

SQL Query join problem

Forum|Forum|16 years ago
July 6, 2009
返信数 2.
2012 ビュー

Hi,

I have two tables in my db, the patient table holds details of patients, the contact table holds details of contacts related to the patient.

patient table

patientid - primary key - int

patient name - varchar

contact table

contactid - primary key - int

patientid - foreign key from patient table - int

incontact - foreign key from paient table to relate contacts - int

My contact table has the following data:

contactid patientid incontact

2 19 21

2 19 22

I can retrieve data for the patients that have been in contact with patientid 19, by using the following join:

SELECT * FROM PATIENT

LEFT JOIN incontact

ON incontact.incontact = patient.patientid

WHERE incontact.patientid = 19

However, can you please tell me how I can get data from the patients table where the incontact is 21?

So I can get contacts from patients and patients from contacts?

Thankyou

Database access

このトピックへの返信は締め切られました。

D

Dan_Bracuk

Inspiring

If you are able, you should consider re-designing your db so that you have a many to many relationship between patients and contacts. It would make it a lot easier to build queries like the one you are attempting.

M

matthisco作成者

Inspiring

Thanks for your reply Dan.

Could you please point me in the right direction as to how my tables should look?

I was under the impression I could have many to many relationships, as I'm storing the patientid primary key as a foreign key in the contacts table.

Thanks again

D

Dan_Bracuk

Inspiring

Many to many relationships look like this

table t1.

t1id primary key

other fields

table t2

t2id primary key

other fields

table t1_t2

t1id

t2id

other fields

The primary key is t1 and t2. Both also have foreign key constraints to tables t1 and t2.

ilssac

Inspiring

And this did not work for you?

SELECT * FROM PATIENT
LEFT JOIN incontact
ON incontact.incontact = patient.patientid
WHERE incontact.incontact = 21

M

matthisco作成者

Inspiring

Thanks for your reply.

If I use:

SELECT * FROM Patient

LEFT JOIN incontact

on incontact.incontact = patient.patientid

WHERE incontact.incontact = 21

I get both patientid's 21 and 19, but all the data from the patients table is for patientid 21, but I need to get the details for patientid 19

Can you help?

Thanks again

M

matthisco作成者

Inspiring

I've managed to get this working now, I changed the name of the patientid field in the contacts table to a unique name, and it works fine.

However I'm using 2 queries in my page, can anyone tell em if I can combine them somehow?

Here is my code:

<cfquery name="getcontact" datasource="staffdirectory">
SELECT * FROM patient
LEFT JOIN incontact
ON patient.patientid = incontact.related
WHERE incontact.incontact = #patientid#
</cfquery>
<cfif getcontact.recordcount eq 0>
<cfquery name="getcontact" datasource="staffdirectory">
SELECT * FROM patient
LEFT JOIN incontact
ON patient.patientid = incontact.incontact
WHERE incontact.related= #patientid#
</cfquery>

</cfif>

サインアップ

ソーシャルログイン

コミュニティへログイン

ソーシャルログイン

ファイルをウイルススキャンする。

このファイルはダウンロードできません