Xerc Xerc - 3 months ago 5x
SQL Question

SQL Query - Limited by another SQL query of a different data type

I need some help on this one. I have a query that I need to make work but I need to limit it by the results of another query.

SELECT ItemID, ItemNums
FROM dbo.Tables

ItemNums is a varchar field that is used to store the strings of the various item numbers.

This produces the following.

ItemID ItemNums
1 1, 4, 5
2 1, 3, 4, 5
3 2
4 4
5 1

I have another table that has each item number as an INT that I need to use to pull all ItemIDs that have the associated ItemNums

Something like this.

FROM dbo.Tables
WHERE ItemNums IN (4,5)

Any help would be appreciated.


If possible, you should change your database schema. In general, it's not good to store comma delimited lists in a relational database.

However, if that's not an option, here's one way using a join with like:

select *
from dbo.Tables t 
    join dbo.SecondTable st on ', '+t.ItemNums+',' like '%, '+st.ItemNumId+',%'

This concatenates commas to the beginning and end of the itemnums to ensure you only match on the specific ids.