0

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 ?

Kenter
  • 47
  • 1
  • 7

1 Answers1

0

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I actually have an option of not storing quoted values in there. But I want the php client code to be as simple as possible to call. If I had the possibility of changing the insert into table B (but it still has to be a single row), what would be the easiest way to do it ? – Kenter Sep 17 '17 at 04:37
  • 1
    I don't know your PHP code or even PHP. I think storing CSV is a bad idea, and quoting the values is a particularly bad idea. – Tim Biegeleisen Sep 17 '17 at 04:39
  • @user8621037 I added a demo to the query. This should at least let you function while you work on cleaning up your data model. – Tim Biegeleisen Sep 17 '17 at 04:50
  • Thanks! Really appreciate it. I might do as you suggested and just clean up the table structure. The problem is, this is a legacy system and some of this stuff hits code elsewhere which makes it a little problematic to change. – Kenter Sep 17 '17 at 04:56
  • simpler: `','||badges||',' LIKE '%,'||badge||',%'` – CL. Sep 17 '17 at 06:38
  • @CL. Simpler but more concatenations and less efficient. Best would be to avoid the CSV altogether. – Tim Biegeleisen Sep 17 '17 at 06:42
  • @CL. Simpler but more concatenations and less efficient. Best would be to avoid the CSV altogether. – Tim Biegeleisen Sep 17 '17 at 06:42