INNER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

INNER JOIN

Author

Topic

topone
Starting Member

5 Posts

Posted - 2004-08-03 : 07:08:58

I need to retrieve data from a db in access through a vb 6.0 application.
I have the following sql command that at the moment does not was it is supposed to do. The data save in the field Consultation.Clinician
is a merge of two fields in the Clinician table.
So what i would like is to get the two separate fields instead of the one in the consultation table. The link between the two tables is called clinicianId that is present in both tables.
The fields in the Clinician table that I need are Name and Number both with alphanumeric values.

SQLStr= "SELECT Consultation.ConsultId,"
SQLStr = SQLStr & "Consultation.Clinician , "
SQLStr = SQLStr & "Surgery.SurgeryName, "
SQLStr = SQLStr & "Patient.PostCode1, "
SQLStr = SQLStr & "MID$(Patient.PostCode2,1,1) AS PostCode2, "
SQLStr = SQLStr & "Patient.Ethnicity_ID, "
SQLStr = SQLStr & "Patient.Ethnicity_Other_text, "
SQLStr = SQLStr & "DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge "
SQLStr = SQLStr & "FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId) "
SQLStr = SQLStr & "(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID) "
SQLStr = SQLStr & "WHERE ((Consultation.BatchNum) = " & intCurrentBatch & ") "
SQLStr = SQLStr & "ORDER BY Consultation.ConsultId;"

In this code I tried adding a
SQLStr = SQLStr & "Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID "
but then it comes an error in the from section of the sql statement.
I also tried just requesting to pass the Name and number of the Clinician but it returns an error as well.
Any ideas ?
Thanks
Paolo

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-03 : 07:35:14

You need to make sure you specify INNER JOIN every time you do a join, something like the code below.


SQLStr= "SELECT Consultation.ConsultId," 
SQLStr = SQLStr & "Consultation.Clinician , "
SQLStr = SQLStr & "Clinician.Name, "
SQLStr = SQLStr & "Clinician.Number, "
SQLStr = SQLStr & "Surgery.SurgeryName, "
SQLStr = SQLStr & "Patient.PostCode1, "
SQLStr = SQLStr & "MID$(Patient.PostCode2,1,1) AS PostCode2, "
SQLStr = SQLStr & "Patient.Ethnicity_ID, "
SQLStr = SQLStr & "Patient.Ethnicity_Other_text, "
SQLStr = SQLStr & "DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge "
SQLStr = SQLStr & "FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId) "
SQLStr = SQLStr & "(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID) "
SQLStr = SQLStr & "(INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID) "
SQLStr = SQLStr & "WHERE ((Consultation.BatchNum) = " & intCurrentBatch & ") "
SQLStr = SQLStr & "ORDER BY Consultation.ConsultId;"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-03 : 07:37:40

Shouldn't that be:
SQLStr = SQLStr & "(INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID)"

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

topone
Starting Member

5 Posts

Posted - 2004-08-03 : 09:33:10

Thanks, i tried and it still return an error message that says "Syntax Error in From Clause".
any other ideas?
Paolo

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-03 : 23:35:33

Print out the final SQLStr and paste it here.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

topone
Starting Member

5 Posts

Posted - 2004-08-04 : 04:37:37

Ok thanks
Paolo

SELECT Consultation.ConsultId,Consultation.ConsultDate ,
Consultation.ConsultTime , Clinicians.Name, Clinicians.RPSGB, Consultation.PatientID, Consultation.BatchNum, Consultation.MedicineDispensed, Consultation.NotPharmacy, Consultation.NotPharmacyOther, Consultation.AnotherHealthcare, Consultation.HealthcareProvider, Consultation.HealthcareProviderOther, Consultation.PatientPresent,
Consultation.PatientNotPresentReason, Consultation.VoucherNumber, Consultation.VoucherDate, Consultation.WhyNotDispensed, Consultation.WhyNotDispensedOther, Consultation.WhyNotDispensedCondition, Consultation.HowLongEnterData,Surgery.SurgeryName, Patient.PostCode1, MID$(Patient.PostCode2,1,1) AS PostCode2, Patient.Ethnicity_ID, Patient.Ethnicity_Other_text,
DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge (INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID)
FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId)
(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID)
(INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID)
WHERE ((Consultation.BatchNum) = 3) ORDER BY Consultation.ConsultId

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-04 : 09:18:07

mistakes at the first glance:

1. Inner join can't be before FROM. -> (INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID) FROM
2. I doubt MID$(Patient.PostCode2,1,1) is correct.

Go with the flow & have fun! Else fight the flow :)

topone
Starting Member

5 Posts

Posted - 2004-08-05 : 04:23:37

Hello,
my mistake in copying the wrong version.There is no INNER JOIN before from.

The fields in the table Clinicians are
RPSGB,Name and ClinicianID if is of any use.
Thanks
Paolo

SELECT Consultation.ConsultId,Consultation.ConsultDate ,
Consultation.ConsultTime , Clinicians.Name, Clinicians.RPSGB, Consultation.PatientID, Consultation.BatchNum, Consultation.MedicineDispensed, Consultation.NotPharmacy, Consultation.NotPharmacyOther, Consultation.AnotherHealthcare, Consultation.HealthcareProvider, Consultation.HealthcareProviderOther, Consultation.PatientPresent,
Consultation.PatientNotPresentReason, Consultation.VoucherNumber, Consultation.VoucherDate, Consultation.WhyNotDispensed, Consultation.WhyNotDispensedOther, Consultation.WhyNotDispensedCondition, Consultation.HowLongEnterData,Surgery.SurgeryName, Patient.PostCode1, MID$(Patient.PostCode2,1,1) AS PostCode2, Patient.Ethnicity_ID, Patient.Ethnicity_Other_text,
DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge
FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId)
(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID)
(INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID)
WHERE ((Consultation.BatchNum) = 3) ORDER BY Consultation.ConsultId

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-05 : 05:15:39

1. MID$(Patient.PostCode2,1,1) still looks strange to me. what does it do? i've never seen that in sql server.
2. DateDiff(yyyy, Patient.dob,Consultation.ConsultDate) instead of DateDiff('yyyy', Patient.dob,Consultation.ConsultDate).
there are no '' around yyyy.
3. there are no brackets before inner join. it should be like:
FROM Consultation INNER JOIN Patient ON (Consultation.PatientID = Patient.PatId)
INNER JOIN Surgery ON (Patient.SurgeryID = Surgery.SurgeryID)
INNER JOIN Clinicians ON (Consultation.ClinicianID = Clinicians.ClinicianID)

thats all that i see...

Go with the flow & have fun! Else fight the flow :)

topone
Starting Member

5 Posts

Posted - 2004-08-05 : 06:25:05

Hello Spirit1,
with the changes that you suggested the error message is different.
[Syntax Error (missing operation) in query expression '((Patient.SurgeryID = Surgery.SurgeryID)
INNER JOIN Clinicians ON (Consultation.ClinicianID = Clinicians.ClinicianID)'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-05 : 07:22:16

well i tried the query in QA with Parse query and it returned ok.
altough from your error message i don't know where did you get the second bracket ((Patient.SurgeryID = Surgery.SurgeryID).

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources