Question 217825 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – How do I check if all of the tags assigned to the playlist_slides exists in the tags assigned in screens table

For context here is a simplified way of my DB Structure

enter image description here

Table definitions and data sample :

CREATE TABLE `screens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playlist_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `playlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ;


CREATE TABLE `playlist_slides` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playlist_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `resource_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `screens` VALUES (1, 2);
INSERT INTO `playlist` VALUES (2);
INSERT INTO `playlist_slides` VALUES (3, 2);

INSERT INTO `resource_tags` VALUES (1, 1, 1);
INSERT INTO `resource_tags` VALUES (2, 2, 1);
INSERT INTO `resource_tags` VALUES (3, 3, 1);

/*INSERT INTO `resource_tags` VALUES (1, 1, 3);
INSERT INTO `resource_tags` VALUES (3, 3, 3); */

INSERT INTO `tags` VALUES (1);
INSERT INTO `tags` VALUES (2);
INSERT INTO `tags` VALUES (3);

I would like to check if all of the tags assigned to the playlist_slides table exists in the tags assigned in screens table.

Currently I tried doing it like this:

SELECT
  `media_content_array`.*,
  `current_screen_tags`.`screen_tags`
FROM `screens`
RIGHT JOIN (
  SELECT 
    `playlist_slides`.`playlist_id`,
    GROUP_CONCAT(
      playlist_content_tags.tag_id
      ORDER BY playlist_content_tags.tag_id
    ) AS tag_ids
  FROM `playlist_slides`
  LEFT JOIN `playlist_content_tags`
  ON `playlist_slides`.`id` = `playlist_content_tags`.`item_id`
  GROUP BY `playlist_slides`.`playlist_id`
) AS `media_content_array`
ON `media_content_array`.`playlist_id` = `screens`.`playlist_id`
RIGHT JOIN (
  SELECT
    `screens`.`id`,
    GROUP_CONCAT(
      resource_tags.tag_id
      ORDER BY resource_tags.tag_id
    ) AS screen_tags 
  FROM `screens`
  LEFT JOIN `resource_tags`
  ON `screens`.`id` = `resource_tags`.`item_id`
  WHERE `screens`.`id` = ?
  GROUP BY `screens`.`id`
) AS `current_screen_tags`
ON `screens`.`id` = `current_screen_tags`.`id`
WHERE `screens`.`id` = ?
AND `media_content_array`.`tag_ids` LIKE CONCAT('%', `current_screen_tags`.`screen_tags`, '%');

as you can see i used

media_content_array.tag_ids LIKE CONCAT('%', media_content_array.tag_ids, '%'

it worked only if tag_ids is on the right sequence.
Not sure how ill do it any other way, please help, thanks!

Tags:

2

Answers


  1. 0 
    CREATE TABLE `screens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playlist_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `playlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ;


CREATE TABLE `playlist_slides` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playlist_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `resource_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `screens` VALUES (1, 2);
INSERT INTO `playlist` VALUES (2);
INSERT INTO `playlist_slides` VALUES (3, 2);

INSERT INTO `resource_tags` VALUES (1, 1, 1);
INSERT INTO `resource_tags` VALUES (2, 2, 1);
INSERT INTO `resource_tags` VALUES (3, 3, 1);

/*INSERT INTO `resource_tags` VALUES (1, 1, 3);
INSERT INTO `resource_tags` VALUES (3, 3, 3); */

INSERT INTO `tags` VALUES (1);
INSERT INTO `tags` VALUES (2);
INSERT INTO `tags` VALUES (3);

    SELECT 
    ps.`id` AS playlist_slide_id,
    ps.`playlist_id` AS playlist_slide_playlist_id,
    ps_tags.tag_ids AS playlist_slide_tags,
    s.`id` AS screen_id,
    s_tags.tag_ids AS screen_tags,
    IF(
        ps_tags.tag_ids IS NULL,
        'No tags associated with playlist_slides',
        IF(
            s_tags.tag_ids IS NULL,
            'No tags associated with screens',
            IF(
                FIND_IN_SET(ps_tags.tag_ids, s_tags.tag_ids),
                'All tags of playlist_slides are in screens',
                'Not all tags of playlist_slides are in screens'
            )
        )
    ) AS result
FROM `playlist_slides` ps
LEFT JOIN (
    SELECT 
        `playlist_slides`.`id` AS slide_id,
        GROUP_CONCAT(
            `resource_tags`.`tag_id` 
            ORDER BY `resource_tags`.`tag_id`
        ) AS tag_ids
    FROM `playlist_slides`
    LEFT JOIN `resource_tags`
    ON `playlist_slides`.`id` = `resource_tags`.`item_id`
    GROUP BY `playlist_slides`.`id`
) AS `ps_tags`
ON ps.`id` = ps_tags.slide_id

LEFT JOIN `screens` s
ON ps.`playlist_id` = s.`playlist_id`
LEFT JOIN (
    SELECT
        `screens`.`id`,
        GROUP_CONCAT(
            `resource_tags`.`tag_id` 
            ORDER BY `resource_tags`.`tag_id`
        ) AS tag_ids
    FROM `screens`
    LEFT JOIN `resource_tags`
    ON `screens`.`id` = `resource_tags`.`item_id`
    GROUP BY `screens`.`id`
) AS `s_tags`
ON s.`id` = `s_tags`.`id`;
    playlist_slide_id playlist_slide_playlist_id playlist_slide_tags screen_id screen_tags result
    3 2 null 1 1,2,3 No tags associated with playlist_slides

    fiddle

    Login or Signup to reply.
  2. 0

    "I would like to check if all of the tags assigned to the playlist_slides table exists in the tags assigned in screens table."
    Other answers, whilst correct, have built on your existing query… which is exceedingly complex. You can break down this problem into 2 parts:

    1. Find the tag id of of each playlist_slide (using a join)

    2. Checking which tags do not exist in resource_tags (using not in)

      select
    *
from
    playlist_slides ps join resource_tags rt on ps.id = rt.item_id
WHERE   
    rt.tag_id not in (select id from tags)

    Technically though, you don’t even need to do a join, you can just select the tag_id from resource_tag and use the same condition

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search