Unexpected result with 'IN' construct

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 2000 Forums

SQL Server Development (2000)

Unexpected result with 'IN' construct

Author

Topic

tintin
Starting Member

3 Posts

Posted - 2003-10-21 : 14:34:40

Scenario: Trying to return only those records from table 1 where it has a corresponding value in table 2. The ID's are both of datatype int and the foreign key value cannot be NULL. Very simple! You would think!

So can anybody explain why the following statement will not return the desire recordset:

select KeyID_fk from table1_tbl
where KeyID_fk in ( select KeyID_pk from table2_tbl )

...but either of the following statements will:

select KeyID_fk from table1_tbl
where cast(KeyID_fk as int) in ( select KeyID_pk from table2_tbl )

or

select KeyID_fk from table1_tbl
where cast(KeyID_fk as varchar) in ( select KeyID_pk from table2_tbl )

SIDE BAR:
This statement will return a correct recordset if I negate the statement (and if this was the recordset I needed):

select KeyID_fk from table1_tbl
where KeyID_fk not in ( select KeyID_pk from table2_tbl )

What am I missing? I am sure there are a multitude of ways to write this query but I would like to understand why the one above does not work.

TinTin

P.S. What to say hello to member robvolk. I've been gone awhile. My old login was tintin31. Was unable to get in with that one today.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 14:39:10

SELECT KeyID_fk
FROM table1_tbl t1
INNER JOIN table2_tbl t2 ON t1.KeyID_fk = t2.KeyID_pk

BTW, you should reconsider your naming standards. For instance, instead of KeyID_fk and KeyID_pk, use KeyID. It is not necessary to put fk and pk in the name as this can be viewed on an ERD. Tables should also not start with table. That is redundant. If you are trying to differentiate between tables and views, then add v_ to your views for instance.

Tara

tintin
Starting Member

3 Posts

Posted - 2003-10-21 : 14:50:25

Thank you for your response.

This was a proof of concept. In production I do not write my SQL as such. I wrote my SQL this way so that my question would be clear.

Unfortunately, your suggestion does not work. Neither will the following statement:

SELECT KeyID_fk
FROM table1_tbl t1, table2_tbl t2
WHERE t1.KeyID_fk = t2.KeyID_pk

Could this be a SQL Server configuration issue?

TinTin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 14:52:38

Well, we are going to need the DDL of your table then (CREATE TABLE statements). Also, please provide DML for sample data (INSERT INTO statements).

Tara

tintin
Starting Member

3 Posts

Posted - 2003-10-21 : 15:15:37

The tables were created in the following manner (bear in mind that in the real production tables there are multiple columns):

CREATE TABLE [dbo].[Table2_tbl] (
[KeyID_pk] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table1_tbl] (
[KeyID_fk] [int] NOT NULL
) ON [PRIMARY]
GO

The insert statements used are typical, i.e:

insert into table2_tbl ( column1, column2 )
values ( @column1, @column2 )

insert into table1_tbl ( KeyID_fk, column1 )
values ( @KeyID, @column1 )
- where @KeyID is the primary key value from table2

Nothing unusual here I think (I hope!)

tintin

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-21 : 15:36:58

show us some insert statements with actual data, insert some data in your tables, and show us the SQL you are trying that fails.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources