SQL Server query column containing list of id's

Question

I am probably over complicating this, but I'm trying to do a query that returns records where one or more id's in a list (ridlist) are present in a column that is also a list of id's (rids).

There is probably a much simpler way to do this but I'm new to it and can't get my head around it.

Pseudoish query:

select boid, rids, address, city, state, zip
from branchoffices
where rids contains one or more of the ids in ridlist

I have branch offices that are responsible for different regions and I need to be able to list out all the branch offices that handle activities in a user selected list of regions.

For example:

branchoffice1's rid field in the db contains 1,13,22
branchoffice2's rid field contains 2,3,4

If the user selects regions 1 and 2, creating a list 2,3. I'd want the query to return only branchoffice2's boid. So i don't think using like % would work.

Tables:

• regions - rid(ident), regionname, some other columns
• branchoffices - boid(ident), rids, city, state, zip, some other columns

Example data:

Regions table (rid, regionname):

"1", "Dallas/Fort Worth"
"2", "Greater Houston"
"3", "Austin"
"4", "San Antonio"
"5", "San Marcos"
etc

Branchoffices table (boid, rids, city, state, phone):

"1", "2,3", "Houston", "TX", "1231231234"
"2", "1", "Fort Worth", "TX", "4561231234"
"3", "4,5", "San Antonio", "TX", "7891231234"

So in the above example data, boid 1 (Houston office) is responsible for the Greater Houston and Austin regions.

Hopefully that makes sense.

Thank you very much for any help and I apologize if I missed this being covered already.

Answer 1

You should have a separate table with one row per branch and per rid. Why is storing ids in a string wrong? Here are some reasons:

• The rid is an integer. It should be stored as an integer, not a string.
• A column (at least using the basic column types) should store only one value.
• Foreign keys should be properly declared, and you cannot do that when the values are the wrong type.
• SQL Server has lousy string functions (just admit it).
• SQL Server cannot optimize the queries very well.
• SQL has this great way of storing lists. It is called a table, not a string.

Sometimes, you are stuck with other people's really, really, realy bad designs. SQL Server has a function that can help you, split_string(). You can use it with a lateral join:

select bo.*
from branchoffices bo cross apply
     (select ss.rid
      from split_string(bo.rids) ss(rid)
      where ss.rid in (1, 2, 3)
     ) ss;

Note that you can also use split_string() on the use input:

with rids as (
      select rid
      from split_string('1,2,3') ss(rid)
     )
select bo.*
from branchoffices bo cross apply
     (select ss.rid
      from split_string(bo.rids) ss(rid) join
           rids
           on ss.rid = rids.rid
     ) ss;