The multi-part identifier "b.encounterid" could no

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

General SQL Server Forums

New to SQL Server Programming

The multi-part identifier "b.encounterid" could no

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-04 : 11:46:43

I get this error when i execute this code. could you help me?

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "b.encounterid" could not be bound.

drop table encounter
go
drop table patient
go
drop table appointment
go

create table patient (patientID int primary key identity(1,1), patientName varchar(50), clientID int)
create table encounter (encounterID int primary key identity(1,1), EncounterName varchar(50), hospitalID int, PatientID int references patient(PatientID))
create table appointment (appointmentID int primary key identity(1,1), HospitalID int, appointmentDate datetime )

insert patient
select 'FeeFee', 100 union all
select 'Max', 100 union all
select 'Bones', 100 union all
select 'Fort', 100

insert encounter
select 'Enc - Daniel', 10, 1 union all
select 'Enc - Jose', 10, 2 union all
select 'Enc - Mary', 11, 3 union all
select 'Enc - Luis', 11, 4

insert appointment
select 12, getdate()-5 union all
select 1, getdate()-3 union all
select 10, getdate()-10

select * from patient
select * from encounter
select * from appointment

select a.appointmentid, b.encounterid from appointment a
where exists (select b.encounterid from encounter b
where a.hospitalID = b.hospitalID
and b.patientID in (select patientID from patient where clientID = 100))

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 11:56:00

You cannot use columns from a WHERE-EXISTS-SELECT in the outer query.

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-04 : 11:56:43

may be u need this?

select a.appointmentid,b.encounterid from appointment a
inner join encounter  b on a.hospitalID = b.hospitalID
inner join patient c on b.patientID = c.patientID
where c.clientID = 100

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-09 : 11:31:57

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources