Skip to main content
M
matthisco
Inspiring
July 6, 2009
Question

SQL Query join problem

  • July 6, 2009
  • 2 replies
  • 2012 views

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

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
July 6, 2009

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
matthiscoAuthor
Inspiring
July 6, 2009

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
July 6, 2009

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
ilssac
Inspiring
July 6, 2009

And this did not work for you?

SELECT * FROM PATIENT
LEFT JOIN incontact
ON incontact.incontact = patient.patientid
WHERE incontact.incontact = 21
M
matthiscoAuthor
Inspiring
July 6, 2009

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
matthiscoAuthor
Inspiring
July 6, 2009

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>
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices