mysql - 仅返回根据查找表验证的记录

Question

我正在使用 MySQL，遇到一个小问题，我正在努力寻找解决方案。我可以使用一些查询通过 PHP 以编程方式实现此目的，但我确信我可以将其作为单个查询来完成:)

我有 3 个表:

1. 产品
2. 产品关键字
3. 有效关键字

每个产品都有多个关键字，这些关键字由“product_keywords”表中的 keyword_id 引用。 “valid_keywords”表仅包含允许的关键字(即作为查找表)。我只需要返回关键字与“valid_keywords”表中的关键字匹配的产品。

有效产品不得包含任何不在“valid_keywords”表中的关键字。

下面是一些测试表/记录:

--表格产品的表格结构

CREATE TABLE `products` (
  `prod_id` int(11) NOT NULL,
  `prod_name` varchar(10) NOT NULL,
  KEY `prod_id` (`prod_id`)
);

-- 表格产品的数据

INSERT INTO `products` (`prod_id`, `prod_name`) VALUES(1, 'Prod 1');
INSERT INTO `products` (`prod_id`, `prod_name`) VALUES(2, 'Prod 2');
INSERT INTO `products` (`prod_id`, `prod_name`) VALUES(3, 'Prod 3');
INSERT INTO `products` (`prod_id`, `prod_name`) VALUES(4, 'Prod 4');

-- 表 product_keywords 的表结构

CREATE TABLE `product_keywords` (
  `prod_id` int(11) NOT NULL,
  `keyword_id` int(11) NOT NULL,
  KEY `prod_id` (`prod_id`),
  KEY `keyword_id` (`keyword_id`)
);

-- 表 product_keywords 的数据

INSERT INTO `product_keywords` (`prod_id`, `keyword_id`) VALUES(1, 1);
INSERT INTO `product_keywords` (`prod_id`, `keyword_id`) VALUES(1, 2);
INSERT INTO `product_keywords` (`prod_id`, `keyword_id`) VALUES(2, 2);
INSERT INTO `product_keywords` (`prod_id`, `keyword_id`) VALUES(2, 4);
INSERT INTO `product_keywords` (`prod_id`, `keyword_id`) VALUES(3, 4);
INSERT INTO `product_keywords` (`prod_id`, `keyword_id`) VALUES(3, 6);
INSERT INTO `product_keywords` (`prod_id`, `keyword_id`) VALUES(4, 7);
INSERT INTO `product_keywords` (`prod_id`, `keyword_id`) VALUES(4, 8);

-- 表valid_keywords的表结构

CREATE TABLE `valid_keywords` (
  `keyword_id` int(11) NOT NULL,
  `keyword_name` varchar(10) NOT NULL,
  KEY `keyword_id` (`keyword_id`)
);

-- 表 valid_keywords 的数据

INSERT INTO `valid_keywords` (`keyword_id`, `keyword_name`) VALUES(2, 'Keyword 2');
INSERT INTO `valid_keywords` (`keyword_id`, `keyword_name`) VALUES(4, 'Keyword 4');
INSERT INTO `valid_keywords` (`keyword_id`, `keyword_name`) VALUES(7, 'Keyword 7');
INSERT INTO `valid_keywords` (`keyword_id`, `keyword_name`) VALUES(8, 'Keyword 8');

对于示例数据，查询应返回产品 2 和 4。

希望你们能给我指出正确的方向。

谢谢。

Answer 1

获取没有无效关键字的产品。

SELECT prod_id,
       prod_name
FROM   products
WHERE  prod_id NOT IN (SELECT prod_id
                       FROM   product_keywords
                       WHERE  keyword_id NOT IN (SELECT keyword_id
                                                 FROM   valid_keywords));