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.
| Author |
Topic |
|
Dennis Falls
Starting Member
41 Posts |
 Posted - 2007-07-26 : 11:43:17
|
| Could someone please tell me why the following statement works:select * from tblencrypt_patientidwhere patientid IN(select patientid from tblvisitwhere edfacilityid = '123abc')This will return every record in tblencrypt_patientid.The problem is patientid does not exist in tblvisit. If I run just the select patientid from tblvisitwhere edfacilityid = '123abc'I get Invalid Column Name 'patientid'. Further testing shows that any column in the outer query WHERE statement will return the entire recordset if it is used in the SELECT from the nested query.I'm just wondering why this does this. It seems that this could cause problems. We discovered this because patientid used to exist in tblvisit but was recently changed to patient_id. A coworker couldn't understand why he got back millions of records. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-26 : 11:47:25
|
This is a known bug feature with the IN statement. This might be because you can write correlated subqueries in the IN part.Take this as a lesson.Always reference column names with table names when having more than one table in a query!
E 12°55'05.76"N 56°04'39.42" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-26 : 11:48:33
|
Writingselect ep.* from tblencrypt_patientid as epwhere ep.patientid IN(select v.patientid from tblvisit as vwhere v.edfacilityid = '123abc')] will get you the desired error message.
E 12°55'05.76"N 56°04'39.42" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
IN Statement