user3256635 user3256635 - 12 days ago 5
MySQL Question

Get TableA id where TableB id is equal to TableA column which has a string

I have two tables TableA and TableB.

TableA:

id | users |
-------------------
1 | ["2","5","4"]|
2 | NULL |
3 | ["5"] |


TableB

id | Name |
-----------
2 | Jane |
3 | Joe |
4 | John |
5 | Jack |


How can I get a result to show the TableA id where TableB id is in TableA Users?


  • So for Jane = 1

  • for Joe = Null

  • for John = 1

  • and Jack = 1,3


Answer

Something like this should work:

SELECT b.id,
       b.Name,
       COALESCE(a.id, 'NA') AS a_id
FROM TableB b
LEFT JOIN TableA a
    ON FIND_IN_SET(b.id, REPLACE(REPLACE(REPLACE(a.users, '[', ''), ']', ''), '"', '')) > 0

Demo here:

SQLFiddle