Shiva Prakashan Shiva Prakashan - 17 days ago 5
SQL Question

Joining tables on columns with comma separated values

Table1: mas_book_author

author_id | author_name
------------+----------------
1 | jhon
2 | roobini
3 | virat
4 | sachin
5 | siva
6 | priya


Table4: mas_bk_accession_entry

id(int)(pk) | author_ids(varchar)
-------------+-------------------
1 | 1,5
2 | 5,1
3 | 2,5
4 | 3,5
5 | 4,5


my result should be like this:

author_name |count
------------+------
jhon,siva | 2


This is my SQL Query

select b.author_name,COUNT(*) as totbook
from mas_bk_accession_entry a
join mas_book_author as b
on b.author_id in (select cast((unnest(string_to_array(f.author_ids,',')) ) as int) as author_ids
from mas_bk_accession_entry f)
where a.author_ids = '1,5'
group by b.author_name

Answer

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.