Join a table that depends on another table-CodePudding

I have a POST table, a CATEGORY table, a TAG table and a MIGTATION_TAG table, I explain the MIGTATION_TAG table contains the movement of the tags between the categories, for example the tag whose ID = 1 belongs to the category whose l 'ID = 10 if I change its category to 12 a line will be added to the MIGTATION_TAG table as follows: ID 1 TAG_ID 1 CATEGOTY_ID 12

the POST table

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

the CATEGORY table

 id         name      
----------  ----------  
 1          category_1      
 2          category_2
 3          category_3

the TAG table

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

the MIGTATION_TAG table

 id         tag_id      category_id
----------  ----------  ----------------
 9          1           3
 8          5           1
 7          1           2
 5          3           1
 4          2           2
 3          5           3
 2          3           3
 1          1           3

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

in some cases if there has been no change of category for a tag then it keeps its first category, I manage to join the TAG table to the POST table via LEFT JOIN but the problem is that the join must depend on the MIGTATION_TAG table which must check if there has been a migration, if so then it must bring me back the last MAX (tag_id ) for each tag ,

here is my query

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

                from POST
                left join TAG ON POST.tag_id = TAG.id
                
                left join  (
                    select id, MAX(tag_id) tag_id 
                    from MIGTATION_TAG 
                    group by id, tag_id
                ) MIGTATION_TAG 
                ON TAG.id = MIGTATION_TAG.tag_id

                left join CATEGORY on MIGTATION_TAG.category_id = CATEGORY.id         
            )
            GROUP BY category
;

here is the result i want to display with my query

Important ! for the post with id = 6 the tag_id = 4 whish was not changed so it will be using the fist_category_id in TAG table

 category   numer_of_posts     
----------  --------------  
category_1  3    
category_2  1
category_3  2

Best regards

CodePudding user response：

You can use:

SELECT MAX(c.name) AS category,
       COUNT(*)
FROM   post p
       INNER JOIN tag t
       ON (p.tag_id = t.id)
       LEFT OUTER JOIN (
         SELECT tag_id,
                MAX(category_id) KEEP (DENSE_RANK LAST ORDER BY id) AS category_id
         FROM   migration_tag
         GROUP BY tag_id
       ) m
       ON (t.id = m.tag_id)
       INNER JOIN category c
       ON ( COALESCE(m.category_id, t.first_category_id) = c.id )
GROUP BY c.id
ORDER BY category

Which, for the sample data:

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

CREATE TABLE CATEGORY  ( id, name ) AS
  SELECT 1, 'category_1' FROM DUAL UNION ALL
  SELECT 2, 'category_2' FROM DUAL UNION ALL
  SELECT 3, 'category_3' FROM DUAL;

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

CREATE TABLE  MIGRATION_TAG ( id, tag_id, category_id ) AS
  SELECT 9, 1, 3 FROM DUAL UNION ALL
  SELECT 8, 5, 1 FROM DUAL UNION ALL
  SELECT 7, 1, 2 FROM DUAL UNION ALL
  SELECT 5, 3, 1 FROM DUAL UNION ALL
  SELECT 4, 2, 2 FROM DUAL UNION ALL
  SELECT 3, 5, 3 FROM DUAL UNION ALL
  SELECT 2, 3, 3 FROM DUAL UNION ALL
  SELECT 1, 1, 3 FROM DUAL;

Outputs:

CATEGORY	COUNT(*)
category_1	3
category_2	1
category_3	2

fiddle

CodePudding user response：

One option uses a left join to bring the tag table, and the a lateral join to lookup the latest migration, ifi any. We can then use conditional logic:

select coalesce(t2.category_id, t.first_category_id) category, count(*) number_of_posts
from post p
inner join tag t on t.id = p.tag_id
outer apply (
    select mt.category_id
    from migration_tag mt
    where mt.tag_id = p.tag_id
    order by mt.id desc fetch first row only
) t2
group by coalesce(t2.category_id, t.first_category_id)