Joining tables on columns with comma separated values

by

You can simplify your join condition and you need a string_agg() to get the comma separated list of author names:

select string_agg(author_name,','), count(*)
from mas_book_author b
  join mas_bk_accession_entry e on b.author_id = any(string_to_array(author_ids,',')::int[])
where e.author_ids="1,5";

Online example: http://rextester.com/NVNBH72654

But you should really fix your data model. Storing comma separated values like the author_ids column is a really, really bad choice.

Leave a Comment