join a table into a field of a table containing multiple values-CodePudding

I have a POST table and a TAG table, I explain the POST table contains following fields : id , title , content , tags_ids which mean that the field tags_ids can contani multiple tags , for example the POST whose ID = 1, has following tags : tag_1, tag_2 tag_5 separated with ;

POST TABLE

 id         title       content     tag_id
----------  ----------  ----------  ----------
 1          title1      Text...     1; 2; 5
 2          title2      Text...     3
 3          title3      Text...     1; 2
 4          title4      Text...     2; 3; 4
 5          title4      Text...     2; 3; 4
 6          title2      Text...     3

the TAG table

 id         name      
----------  ---------- 
 1          tag_1       
 2          tag_2       
 3          tag_3       
 4          tag_4       
 5          tag_5

so i would like to know how many posts are registered for each case.

Here is my query

select tag, COUNT(*) AS cnt
                  
        from(
            select CATEGORY.name,               
                case
                when POST.tag_id is not null then tag.name
                end as tag

                from POST
                left join TAG ON POST.tag_id = TAG.id
                     
            )
            GROUP BY tag
;

here is the result i want to display with my query

 tag                     cnt
--------------------     --------------  
tag_1, tag_2, tag_5      1
tag_3                    2
tag_1, tag_2             1 
tag_2, tag_3, tag_4      2

Best regards

CodePudding user response：

Here's one option. Read comments within code.

Sample data:

SQL> with
  2  post(id, tag_id) as
  3    (select 1, '1; 2; 5' from dual union all
  4     select 2, '3'       from dual union all
  5     select 3, '1; 2'    from dual union all
  6     select 4, '2; 3; 4' from dual union all
  7     select 5, '2; 3; 4' from dual union all
  8     select 6, '3'       from dual
  9    ),
 10  tag (id, name) as
 11    (select 1, 'tag_1' from dual union all
 12     select 2, 'tag_2' from dual union all
 13     select 3, 'tag_3' from dual union all
 14     select 4, 'tag_4' from dual union all
 15     select 5, 'tag_5' from dual
 16    ),

Query begins here:

 17  post_distinct as
 18    -- number of rows per each distinct TAG_ID
 19    (select tag_id,
 20            count(*) cnt
 21     from post
 22     group by tag_id
 23    ),
 24  temp as
 25    -- split TAG_ID into rows
 26    (select tag_id,
 27            cnt,
 28            trim(regexp_substr(tag_id, '[^;] ', 1, column_value)) tag_id_split
 29     from post_distinct p cross join
 30       table(cast(multiset(select level from dual
 31                           connect by level <= regexp_count(p.tag_id, ';')   1
 32                          ) as sys.odcinumberlist))
 33    )
 34  -- finally, join tables to get the result
 35  select listagg(t.name, ', ') within group (order by t.id) tag,
 36         te.cnt
 37  from tag t join temp te on te.tag_id_split = t.id
 38             join post_distinct p on p.tag_id = te.tag_id
 39  group by p.tag_id, te.cnt
 40  order by p.tag_id;


TAG                         CNT
-------------------- ----------
tag_1, tag_2                  1
tag_1, tag_2, tag_5           1
tag_2, tag_3, tag_4           2
tag_3                         2

SQL>

CodePudding user response：

You do not need to split the posts, to get your expected output you can just aggregate by the tag_ids:

SELECT tag_id,
       COUNT(DISTINCT id) AS num_posts
FROM   post
GROUP BY tag_id;

Which, for the sample data:

CREATE TABLE POST (id, title, content, tag_id) AS
SELECT 1, 'title1', 'Text...', '1; 2; 5' FROM DUAL UNION ALL
SELECT 2, 'title2', 'Text...', '3' FROM DUAL UNION ALL
SELECT 3, 'title3', 'Text...', '1; 2' FROM DUAL UNION ALL
SELECT 4, 'title4', 'Text...', '2; 3; 4' FROM DUAL UNION ALL
SELECT 5, 'title4', 'Text...', '2; 3; 4' FROM DUAL UNION ALL
SELECT 6, 'title2', 'Text...', '3' FROM DUAL;

CREATE TABLE TAG (id, name) AS
SELECT 1, 'tag_1' FROM DUAL UNION ALL
SELECT 2, 'tag_2' FROM DUAL UNION ALL
SELECT 3, 'tag_3' FROM DUAL UNION ALL
SELECT 4, 'tag_4' FROM DUAL UNION ALL
SELECT 5, 'tag_5' FROM DUAL;

Outputs:

TAG_ID	NUM_POSTS
1; 2	1
3	2
1; 2; 5	1
2; 3; 4	2

If you want to convert the ids to names then:

SELECT ( SELECT LISTAGG(name, '; ')
                  WITHIN GROUP (ORDER BY INSTR('; ' || p.tag_id || '; ', '; ' || t.id || '; '))
         FROM   tag t
         WHERE  INSTR('; ' || p.tag_id || '; ', '; ' || t.id || '; ') > 0
       ) AS tags,
       COUNT(DISTINCT id) AS num_posts
FROM   post p
GROUP BY tag_id;

Which outputs:

TAGS	NUM_POSTS
tag_1; tag_2	1
tag_1; tag_2; tag_5	1
tag_2; tag_3; tag_4	2
tag_3	2

If you want to count the posts for each individual tag then:

SELECT t.id,
       COUNT(DISTINCT p.id) AS num_posts
FROM   post p
       INNER JOIN tag t
       ON ('; ' || p.tag_id || '; ' LIKE '%; ' || t.id || '; %')
GROUP BY t.id

Which outputs:

ID	NUM_POSTS
2	4
4	2
3	4
5	1
1	2

fiddle