IN not working - 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 2000 Forums

SQL Server Development (2000)

IN not working

Author

Topic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2004-11-24 : 04:35:40

Hi, I have two tables called Login and Group. Login table has the following records

Logid Name
-------------
1 N1
2 N2
3 N3

Group table has the following records

Logid GroupName
-------------------------
'1','2','3' Test

All the fields of both table are of nvarchar data type

I used the query

select * from login where logid in('1','2','3')

which gave the required result. But when using this query

select * from login where logid in(select logid from trans)

nothing resulted

What goes wrong?

Madhivanan

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-24 : 04:50:57

quote:
Originally posted by madhivanan

Hi, I have two tables called Login and Group. Login table has the following records

Logid Name
-------------
1 N1
2 N2
3 N3

Group table has the following records

Logid GroupName
-------------------------
'1','2','3' Test

All the fields of both table are of nvarchar data type

I used the query

select * from login where logid in('1','2','3')

which gave the required result. But when using this query

select * from login where logid in(select logid from trans)

nothing resulted

What goes wrong?

Madhivanan

does trans.logid have the corresponding entries?

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2004-11-24 : 05:21:34

Its is not trans. It should be group.

select * from login where logid in(select logid from Group)

Madhivanan

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-24 : 06:44:22

that's becuase you column entry is '1','2','3'
so the sql gets translated in:
select * from login where logid in ('''1','2','3''')
so it looks for the string "'1','2','3'" on t for the each number.
parse the string into integers with split function that you can find on this site. use search.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-24 : 07:31:41

Does group really have to be designed like that.

If it where a one to many, you could just join login to group

ie
Logid GroupName
-------------------------
'1' Group1
'2' Group1
'3' Group1
'2' Group2
'3' Group3

Select * From login A inner join group B on A.logId = B.logId and B.groupName = 'Group1'

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources