0

I have the following tables:

Announcements:
id | title | body
-----------------------
1  | test1 | test body1
2  | test2 | test body2
3  | test3 | test body3

Tags:
id | title | parent_id
-------------------------
1  | tag1  | (NULL)
2  | tag2  | 1
3  | tag3  | 2

And their pivot table:

Announcement_tag:
announcement_id | tag_id
1               | 1
2               | 2
3               | 3

I want the pivot table to look like this:

Announcement_tag:
announcement_id | tag_id
1               | 1
2               | 1
2               | 2
3               | 1
3               | 2
3               | 3

They decided to add auto insert for parent's tags for the new announcement system, but now the old announcements are combined with the new ones and some of them have parent's tags inserted and others don't. The problem is that they don't want to delete the old announcements. So now I need to find a way check if the announcement has tags with their parents or not and if it hasn't insert them into announcement_tag table.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
George Stinis
  • 1
  • 1

1 Answers1

0

Found a solution that kinda works:

INSERT INTO announcement_tag (announcement_id, tag_id) 
   SELECT 
      a_t.`announcement_id`, t.`parent_id` 
   FROM announcement_tag AS a_t 
   INNER JOIN tags AS t 
   ON a_t.`tag_id` = t.`id` 
   WHERE t.`parent_id`!= 1 
   AND a_t.`announcement_id` IN (
       SELECT announcement_id FROM announcement_tag GROUP BY announcement_id 
       HAVING COUNT(tag_id) = 1
   );
George Stinis
  • 1
  • 1
  • You should properly format the code in your answer. As it stands, it is just a single, long line. (1) Enclose the code in fences (three back-ticks on lines before and after). (2) Consider adding line-breaks and indentation to the code itself. – Adrian Mole Nov 30 '21 at 13:04