Multi Table SQL

[Compass1]

Hi every body



I use MS Accress 2003 and VB.net 2005:



I have 3 tables



1. tblPatient

PateintID PatientName

1 John



2. tblPatient-EmetLink

PatientID EmergencyContactID

1 2



3. tblEmergencyCont

EmergencyContactID Surname

2 Beeter



The relationship between tables tblPatient and tblEmergencyCont is Many TO Many and tblPatient-EmetLink is used to link the 2 tables.



I am trying to Retrieve all the Emergency Contact for a particular patient from tblEmergencyCont.

I assume the SQL should be as following but I no it is not correct.



strEmegencyContact = "Select * From tblPatientRegister, tblPatient-EmetLink, tblEmergencyCont Where tblPatientRegister.PatientID = tblPatient-EmetLink.PatietID And tbltblEmergencyCont.EmergecyID = tblEmergencyContact.EmergecyID



Please Correct it for ME

 

[cjard]

Looks like you forgot to add the actual patient id you wanted:

Select 
  * 
From 
  tblPatientRegister r
  INNER JOIN
  tblPatient-EmetLink l
  ON
    r.PatientID  = l.[B]PatietID [/B]

  INNER JOIN
  tblEmergencyCont e
  ON
    e.[B]EmergecyID [/B]= l.[B]EmergecyID[/B]

Where 
  r.PatientID = ?

then add a parameter to the command:

cmd.Parameters.AddWithValue("patientID", THE_PATIENT_ID_GOES_HERE)


Note: in the SQL, i have put items in bold that have BAD SPELLING. Check the spelling? Emergecy? PatietID? Does your N key not work properly?