Query- - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query-

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-11-15 : 09:07:30

Gurus,

Why i am not getting error in the follwoing query. The data1 column doesnt exits in tbl2 but it is not giving the error

CREATE TABLE TBL1
(
Id1 int IDENTITY(1,1) PRIMARY KEY,
data1 int
)

CREATE TABLE TBL2
(
Id int IDENTITY(1,1) PRIMARY KEY,
Id1 int, data2 int )

Select * from TBL1 Where Id1 IN (Select data1 from TBL2)

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 09:22:07

because in tbl2 you have the field data2 not data1.

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 09:23:01

ignore that.
:)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 09:25:09

quote:

jamie

because in tbl2 you have the field data2 not data1.

i misread it at first too. OP is saying they DON'T get an error. i tried it and was surprised too actually. i can only guess that it's somehow 'recognised' it as valid from tbl1. not come accross this before though

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 09:28:24

it does throw an error if you prefix it with the table name ... i.e. tbl2.data1

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 09:28:26

bacause data1 is a valid column in TBL1

If you prefix the column name with table name or alias, you will see if return you an error


Select * from TBL1 Where Id1 IN (Select t2.data1 from TBL2 t2)

KH
[spoiler]Time is always against us[/spoiler]

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-15 : 09:28:34

Correct, this is an SQL trap.
In a subquery, all columns from the upper table are still visible and accesible

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 09:30:41

also try this

CREATE TABLE TBL1
(
	Id1 	int IDENTITY(1,1) PRIMARY KEY,
	data1 	int
)

CREATE TABLE TBL2
(
	Id 	int IDENTITY(1,1) PRIMARY KEY,
	Id1 	int, 
	data2 	int 
)

INSERT INTO TBL1 (data1)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 30

INSERT INTO TBL2 (Id1, data2)
SELECT	100, 200

SELECT * FROM TBL1 WHERE Id1 IN (SELECT data1 FROM TBL2)

DROP TABLE TBL1
DROP TABLE TBL2