SQL Query join problem

[matthisco]

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

[ilssac]

And this did not work for you?

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

[matthisco]

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

[matthisco]

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>

[Dan_Bracuk]

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.

[matthisco]

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

[Dan_Bracuk]

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.

[matthisco]

Thanks for your reply.

Thats what I have done in my db design I think, the primary key of the patient is stored as an int field in my contacts table.

Patients can then be assigned to each other, how ever many times. Unless I have misundewrstood.

In my first post I've tried to show how my tables are designed.

Is there a way I can turn my sql into one query?

Thanks again

[Dan_Bracuk]

Having the patient id as a foreign key in the contacts table sets up a one to many relationship.  Each contact has only one patient and each patient can have many contacts.

Your OP also mentions this field in the contact table.

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

What does it represent and how does it get populated?

[ilssac]

I think the op's contact table is a many-to-many join between patient records.  If you look at the table in the first post, there was the ability to create multiple records in this table to join several patient records together.

The name could probably be more descriptive of the join relationhsip, but it looks like it is there in design.

[matthisco]

Thanks very much for your replies again.

Basically, I have a table of patients, and I would like to link them to each other, many to many.

I populate the incontact table, which stores the original posted patientID, and the new generated patientID. I populate the DB with the code below.

<cfquery name="update" datasource="staffdirectory">
SET NOCOUNT ON
INSERT INTO patient (title,firstname,surname,gender,DOB,telephone,address1,address2,address3,town,postcode

)
VALUES ('#title#','#firstname#','#surname#','#gender#',#createodbcdate(createdate(startyear, startmonth, startday))#    ,'#telephone#','#address1#','#address2#','#address3#','#town#','#postcode#

)
SELECT @@Identity AS newid   
SET NOCOUNT OFF
</cfquery>
      <cfquery name="insert" datasource="staffdirectory">

  INSERT INTO incontact (related,incontact)
VALUES (#patientid#,#update.newid#)

</cfquery>

This works fine and I can get all related patients by usingthe following query:

SELECT * FROM patient
LEFT JOIN incontact
ON patient.patientid  = incontact.related
WHERE  incontact.incontact = #patientid#

MY problem occurs when I want to find related patients from the related column, the reverse relationship. I use this seperate query:

SELECT * FROM patient
LEFT JOIN incontact
ON patient.patientid  = incontact.incontact
WHERE  incontact.related= #patientid#

Is there a way I can combine both queries, or is there a better way I can achieve the same result?

Many thanks again

[Dan_Bracuk]

select stuff

from patient p1 join incontact i on p1.patient.id = i.patientid

join patient p2 on i.incontact = p.patientid

where p1.patientid = your patient

union

select stuff

from patient p join incontact i on p.patient.id = i.patientid

where i.incontact = your patient