How to find rows where subquery string contains main query string?

Question

For example, here are 2 tables with 1 row each:

Table Person

id   age
0    30 
1    41
2    15

Table People

ids    group
0,2    a
1      b

How would I make this query work?

SELECT id, age FROM Person WHERE ID like (select ids FROM People WHERE ids = '0,2')

This should list id 0 and 2 rows from the first table, i.e. any ID contained in "0,2"

id   age
0    30 
2    15

Is there an easy way to do this?

Answer 1

You first effort should go into fixing your data model. You should not be storing multiple numeric values in a string column.

If a person may belong to a single group, you can just add a column in the person table that represents the id of the relevant group:

• Table groups:

id   name
1    a
2    b

• Table person:

id   age   group_id
1    30    1
2    41    1
3    15    2

If a person may belong to may groups, you should have a separate table, where each (id, grp) tuple is stored on a separate row.

• Table groups:

id   name
1    a
2    b

• Table person:

id   age
1    30 
2    41
3    15

• Table person_groups:

id_person    id_group
1            1
2            1
3            1
3            2

With both set-ups, it is easy to write a query that retrieves the member of a given group, or to join the tables together.

---

As far as your current design is concerned: I think that you are looking for a join condition across the tables. One option is:

SELECT pn.id, pn.age 
FROM Person pn 
INNER JOIN People pe ON ',' + pe.ids + ',' LIKE '%,' + pn.id + ',%'
WHERE pe.group = 'a'

Or using an exists condition:

SELECT pn.* 
FROM Person pn 
WHERE EXISTS (
    SELECT 1
    FROM People pe 
    WHERE
        ',' + pe.ids + ',' LIKE '%,' + pn.id + ',%'
        AND pe.group = 'a'
)