0

I have query like below

select * from table1 where id in (select ids from table2 where id = 100)

The subquery returns me output like 34,35,55,66 if i run it individually.

But my above query gives error as 

Conversion failed when converting the varchar value '34,35,55,56' to data type int.

The id column in table1 is int. I am aware that i can do this in 2 seperate queries but if there is any way to do in single query then please let me know. Many thanks in advance

DevelopmentIsMyPassion
  • 3,541
  • 4
  • 34
  • 60
  • 1
    Please don't store multiple values in one column. That is a real pain like in your case. You should really change your DB structure. – juergen d Jun 24 '13 at 13:05
  • @juergend no i cant change as its existing database. – DevelopmentIsMyPassion Jun 24 '13 at 13:06
  • Even an existing DB can be changed... Please think about doing it even if you have to change a few things. – juergen d Jun 24 '13 at 13:08
  • 2
    There is a logical difference between a *single* string that happens to contain sets of digits and commas, and *multiple* integers. This is as true in SQL as it is in practically any other language I can think of. It really would be better to change the structure, because you'll continue to hit pain points with this current design. If you insist on continuing, you need to look up splitting strings on commas - which has been asked and answered *plenty* of times on here. – Damien_The_Unbeliever Jun 24 '13 at 13:09
  • @Damien_The_Unbeliever can you give me link where we can split string in commas – DevelopmentIsMyPassion Jun 24 '13 at 13:12
  • [Your](http://stackoverflow.com/questions/12999631/split-string-by-comma-in-sql-server-2008) [Search](http://stackoverflow.com/questions/10581772/sql-server-2008-how-to-split-a-comma-separated-value-to-columns) [Engine](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) [Doesn't](http://stackoverflow.com/questions/9044011/sql-server-split-by-comma) [Work](http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql)? – Damien_The_Unbeliever Jun 24 '13 at 13:16
  • @Damien_The_Unbeliever i am searching but found this one http://www.codeproject.com/Articles/30681/Split-parameter-string-from-comma-seperator-in-SQL . But this is asking to create some user defined function. Searching for more. If you know anything better do let me kno – DevelopmentIsMyPassion Jun 24 '13 at 13:18
  • It looks like i need to either define user define function or call 2 seperate queries. There is no way out. – DevelopmentIsMyPassion Jun 24 '13 at 13:49
  • Are you starting to believe what I previously stated "... you'll continue to hit pain points ..."? – Damien_The_Unbeliever Jun 24 '13 at 14:06

1 Answers1

1

I don't know about sql server and the comments about this kind of structure being undesirable notwithstanding, you can do it in mysql with the following, per this fiddle.

select distinct id from table1 join table2 on find_in_set(id,ids)

Update: I just realized this doesn't reflect table2 having it's own id field and restricting the search based on that. If you need help modifying the query to accomodate let me know and I'll update it and the fiddle.

Update 2: Here's a lame but perhaps valid attempt to do this in SQL Server with its much more limited string facilities, per this fiddle. If anyone knows of a simpler find_in_set equivalent for SQL Server, I'd be curious to know what it is.

select distinct id from table1 join table2 
  on patindex('%,'+cast(id as varchar)+',%',ids)>0 or
  patindex(cast(id as varchar)+',%',ids)>0 or
  patindex('%,'+cast(id as varchar),ids)>0 or
  patindex(cast(id as varchar),ids)>0
Peter Alfvin
  • 28,599
  • 8
  • 68
  • 106