Search code examples
sqldatabasesinglestore

Joining two tables with string of integers using SingleStore

Let's say we are given two tables: zoo and animals. zoo contains a string of integers representing the ids of animals, ex: '1,2,3', and a corresponding table for animals: 1:'iguana', etc.

create table zoo (zooid int, animals varchar(10));
insert into zoo values
(1,'1,2'),(2,'1,2,4');

create table animals (animalid  int, animal varchar(10));
insert into animals values
(1,'Bear'),(2,'Wolf'),(3,'Pig'),(4,'Lion');

How can someone join these two tables such that for a zoo id, the animals columns has varchar(100) 'Bear, Lion'? For example:

zooid animals
1 'Bear, Wolf'
2 'Bear, Wolf, Lion'

Using MySQL you could probably use FIND_IN_SET() but we use SingleStore. I know that the data is not normalized but unfortunately I don't have any say in that. Thank you!

Solution

  • You can accomplish this with the SPLIT and TABLE builtins. SPLIT is a builtin that lets you transform a comma (or other separator) separated list into an array and then TABLE is a TVF that will transform that into a relation:

    select  zooid, 
        group_concat(animal) 
from zoo 
join TABLE(SPLIT(zoo.animals,',')) 
join animals on animalid = table_col 
group by 1