Multi-part Identfier could not be found (with SQL Script)

Hi all,

I have attached the following script from which the error message come from.

Please help me out.

use PatientCare

go

select pr.PatientId, p.FirstName,p.LastName, m.Name

from Prescription as pr inner join Patient as p inner join Medicine as m

on p.PatientId = pr.PatientId

on pr.MedicineCode = m.MedicineCode;

Error message got is as below:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "pr.PatientId" could not be bound.

Thanx in advance.

Ronald

Ronaldlee Ejalu wrote:

use PatientCare

go

select pr.PatientId, p.FirstName,p.LastName, m.Name

from Prescription as pr inner join Patient as p inner join Medicine as m

on p.PatientId = pr.PatientId

on pr.MedicineCode = m.MedicineCode;

SELECT pr.PatientID, p.FirstName, p.LastName, m.Name

FROM Prescription pr

JOIN Patient p ON p.PatientID = pr.PatientID

JOIN Medicine m ON pr.MedicineCode = m.MedicineCode

or you can do it this way

SELECT pr.PatientID, p.FirstName, p.LastName, mName

FROM Prescription pr, Patient p, Medicine m

WHERE p.PatientID = pr.PatientID AND pr.MedicineCode = m.MedicineCode

Adamus

|||

hi ronald

looks like you got a syntax error and adamus is right

i deleted you other post cause it looks identical to this one

ragards

|||

Just to add what to has been said. You have nested joins, which I have bolded here:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join Patient as p
inner join Medicine as m
on p.PatientId = pr.PatientId
on pr.MedicineCode = m.MedicineCode;

This nested join has to operate first, then the next join:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join PatientMedicineJoinResults
on pr.MedicineCode = m.MedicineCode;

The point of this is to simplify how queries can be written when you are joining together tables, especially for lookup tables where there are outer joins involved:

select <columnlist>
from table
left outer join table2
inner join table2Lookup
on table2.table2LookupId = table2Lookup.table2LookupId
on table.tableKey = table2.tableKey
left outer join table3
inner join table3Lookup
on table3.table3LookupId = table3Lookup.table3LookupId

Not, of course that any of this matches what you need :) In your case, you are joining each of the tables to the prescription table, so the join criteria must match that, and not be nested:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join Patient as p
on p.PatientId = pr.PatientId
inner join Medicine as m
on pr.MedicineCode = m.MedicineCode;

|||

Hi all,

I really thank you for this.

It worked.