
DROP TABLE IF EXISTS `albums_tags`;
DROP TABLE IF EXISTS `tags`;
DROP TABLE IF EXISTS `albums`;
DROP TABLE IF EXISTS `artists`;

CREATE TABLE `artists` (`id` integer PRIMARY KEY, `name` varchar(255));
CREATE TABLE `albums` (`id` integer PRIMARY KEY, `name` varchar(255), `artist_id` integer, FOREIGN KEY (`artist_id`) REFERENCES `artists`(`id`));
CREATE TABLE `tags` (`id` integer PRIMARY KEY, `name` varchar(255));
CREATE TABLE `albums_tags` (`album_id` integer, `tag_id` integer, FOREIGN KEY (`album_id`) REFERENCES `albums`(`id`), FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`));

INSERT INTO `artists` (`id`, `name`) VALUES (1, 'Ar'), (2, 'Ar');
INSERT INTO `albums` (`id`, `name`, `artist_id`) VALUES (1, 'Al', 1), (2, 'Al', 2);
INSERT INTO `tags` (`id`, `name`) VALUES (1, 'T'), (2, 'T');
INSERT INTO `albums_tags` (`album_id`, `tag_id`) VALUES (1, 1), (2, 2);

SELECT * FROM artists;

        SELECT `albums`.`artist_id` AS `b`,
               `tags`.`id` AS `c`,
               row_number() OVER (PARTITION BY `albums`.`artist_id` ORDER BY `tags`.`name`) AS `x_sequel_row_number_x`
        FROM `tags`
        INNER JOIN `albums_tags` ON (`albums_tags`.`tag_id` = `tags`.`id`)
        INNER JOIN `albums` ON (`albums`.`id` = `albums_tags`.`album_id`)
        WHERE (`tags`.`name` = 'T');

      SELECT `b`, `c` FROM (
        SELECT `albums`.`artist_id` AS `b`,
               `tags`.`id` AS `c`,
               row_number() OVER (PARTITION BY `albums`.`artist_id` ORDER BY `tags`.`name`) AS `x_sequel_row_number_x`
        FROM `tags`
        INNER JOIN `albums_tags` ON (`albums_tags`.`tag_id` = `tags`.`id`)
        INNER JOIN `albums` ON (`albums`.`id` = `albums_tags`.`album_id`)
        WHERE (`tags`.`name` = 'T')
      ) AS `t1`
      WHERE (`x_sequel_row_number_x` = 1)
      ORDER BY `x_sequel_row_number_x`;

WITH foo AS (
  SELECT `albums`.`artist_id`
  FROM `tags`
  INNER JOIN `albums_tags` ON (`albums_tags`.`tag_id` = `tags`.`id`)
  INNER JOIN `albums` ON (`albums`.`id` = `albums_tags`.`album_id`)
  WHERE ((`tags`.`name` = 'T')
    AND (`albums`.`artist_id` IS NOT NULL)
    AND ((`albums`.`artist_id`, `tags`.`id`) IN (
      SELECT `b`, `c` FROM (
        SELECT `albums`.`artist_id` AS `b`,
               `tags`.`id` AS `c`,
               row_number() OVER (PARTITION BY `albums`.`artist_id` ORDER BY `tags`.`name`) AS `x_sequel_row_number_x`
        FROM `tags`
        INNER JOIN `albums_tags` ON (`albums_tags`.`tag_id` = `tags`.`id`)
        INNER JOIN `albums` ON (`albums`.`id` = `albums_tags`.`album_id`)
        WHERE (`tags`.`name` = 'T')
      ) AS `t1`
      WHERE (`x_sequel_row_number_x` = 1)
      ORDER BY `x_sequel_row_number_x`
      )
    ))
)
SELECT * FROM foo;

WITH foo AS (
  SELECT `albums`.`artist_id`
  FROM `tags`
  INNER JOIN `albums_tags` ON (`albums_tags`.`tag_id` = `tags`.`id`)
  INNER JOIN `albums` ON (`albums`.`id` = `albums_tags`.`album_id`)
  WHERE ((`tags`.`name` = 'T')
    AND (`albums`.`artist_id` IS NOT NULL)
    AND ((`albums`.`artist_id`, `tags`.`id`) IN (
      SELECT `b`, `c` FROM (
        SELECT `albums`.`artist_id` AS `b`,
               `tags`.`id` AS `c`,
               row_number() OVER (PARTITION BY `albums`.`artist_id` ORDER BY `tags`.`name`) AS `x_sequel_row_number_x`
        FROM `tags`
        INNER JOIN `albums_tags` ON (`albums_tags`.`tag_id` = `tags`.`id`)
        INNER JOIN `albums` ON (`albums`.`id` = `albums_tags`.`album_id`)
        WHERE (`tags`.`name` = 'T')
      ) AS `t1`
      WHERE (`x_sequel_row_number_x` = 1)
      ORDER BY `x_sequel_row_number_x`
      )
    ))
)
SELECT *
FROM `artists`
WHERE (`artists`.`id` IN ( SELECT * FROM foo));
