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