SQLite query with literal values

Question

I have a 2 tables A and B in SQLite

A:

Name|Badge
----------
ABC |X
XYZ |Y

B:

ListCode |Badges
--------------------
V        |'X','Y','Z'

I want to do something like this:

SELECT * FROM A WHERE BADGE IN (SELECT BADGES FROM B WHERE LISTCODE = 'V');

The problem is SELECT IN doesnt seem to work like that with literal csv from another table. How can I do this ?

Answer 1

The moral of the story here is to not store CSV data as you have been doing. This answers assumes that you are actually storing unquoted CSV data, i.e. this:

X,Y,Z

That being said, you can workaround this by using the following query:

SELECT A.*
FROM A
INNER JOIN B
    ON A.BADGE = B.BADGES OR
       B.BADGES LIKE A.BADGE || ',%' OR
       B.BADGES LIKE '%,' || A.BADGE || ',%' OR
       B.BADGES LIKE '%,' || A.BADGE
WHERE
    B.BADGES IS NOT NULL

Here is a demo of the above query. It is in MySQL because Rextester does not support SQLite but the logic and data are identical.

Demo