mysql - 如何在关系表的同一列中获取具有两个结果的项目

Question

我有一个食谱数据库，其中包含午餐、晚餐、甜点、无糖等类别，我想返回例如无糖和甜点的食谱。

Recipes
+----+------------------+
| id | name             |
+----+------------------+
| 12 | cookies          |
| 15 | spaghetti        |
| 22 | sugar-free tarts |
+----+------------------+


Categories
+----+------------+
| id | name       |
+----+------------+
|  1 | dinner     |
|  2 | dessert    |
|  3 | sugar free |
+----+------------+

Recipe Categories
+----+-----------+-------------+
| id | recipe_id | category_id | 
+----+-----------+-------------+
|  1 |        12 |      2      |
|  2 |        15 |      1      |
|  3 |        15 |      3      |
|  4 |        22 |      2      |
|  5 |        22 |      3      |
+----+-----------+-------------+

我试过使用这个查询，但我得到的食谱要么是甜点，要么是无糖的(如果我们使用示例图像数据，则返回意大利面条)

SELECT r.* 
FROM recipes r 
JOIN recipe_categories rc ON r.id = rc.recipe_id 
WHERE rc.category_id ='2' OR rc.category_id ='3'
GROUP BY recipe_id 
HAVING COUNT(rc.id)>=2 
ORDER BY r.created_on DESC

问题演示:

CREATE TABLE recipes (
  `id` INTEGER,
  `name` VARCHAR(16)
);

INSERT INTO recipes
  (`id`, `name`)
VALUES
  ('12', 'cookies'),
  ('15', 'spaghetti'),
  ('22', 'sugar-free tarts'),
  ('41', 'Creamy Barley'),
('42', 'Tomato Salad'),
('112', 'Freddy Potatoes'),
('181', 'Guacamole'),
('196', 'Tzatziki'),
('214', 'Soft Boiled Eggs');

CREATE TABLE `recipe_category_names` (
  `id` int(11) NOT NULL,
  `name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `parent_recipe_category_id` int(1) DEFAULT NULL,
  `is_primary` int(11) DEFAULT NULL,
  `deleted_on` datetime DEFAULT NULL,
  `deleted_by` int(11) DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_on` datetime NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL DEFAULT 666
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `recipe_category_names`
--

INSERT INTO `recipe_category_names` (`id`, `name`, `parent_recipe_category_id`, `is_primary`, `deleted_on`, `deleted_by`, `updated_on`, `updated_by`, `created_on`, `created_by`) VALUES
(2, 'Dessert', 80, 1, NULL, NULL, '2015-07-31 15:07:09', 1, '2015-02-06 01:04:00', 1),
(3, 'Sugar Free', 81, NULL, NULL, NULL, '2015-07-31 15:07:27', 1, '2015-03-10 00:00:00', 1);


CREATE TABLE `recipe_categories` (
  `id` int(11) NOT NULL,
  `recipe_id` int(11) NOT NULL,
  `recipe_category_name_id` int(11) NOT NULL,
  `deleted_on` datetime DEFAULT NULL,
  `deleted_by` int(11) DEFAULT NULL,
  `updated_on` varchar(200) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL
);

INSERT INTO `recipe_categories` (`id`, `recipe_id`, `recipe_category_name_id`, `deleted_on`, `deleted_by`, `updated_on`, `updated_by`, `created_on`, `created_by`) VALUES

(2, 22, 3, NULL, NULL, '0000-00-00 00:00:00', 10, '2016-02-13 15:19:23', 10),
(3, 22, 2, NULL, NULL, '0000-00-00 00:00:00', 0, '2019-05-10 21:48:17', 10),
(1582, 42, 3, NULL, NULL, '0000-00-00 00:00:00', 10, '2016-02-13 15:19:23', 10),
(4610, 112, 3, NULL, NULL, '0000-00-00 00:00:00', 0, '2019-05-10 21:48:17', 10),
(4609, 112, 16, NULL, NULL, '0000-00-00 00:00:00', 0, '2019-05-10 21:48:17', 10),
(3866, 214, 78, NULL, NULL, '0000-00-00 00:00:00', 0, '2018-06-30 12:58:09', 10),
(1581, 42, 16, NULL, NULL, '2016-02-13 15:19:23', 10, '2016-02-13 15:19:23', 10),
(4608, 112, 7, NULL, NULL, '0000-00-00 00:00:00', 0, '2019-05-10 21:48:17', 10),
(1580, 42, 7, NULL, NULL, '2016-02-13 15:19:23', 10, '2016-02-13 15:19:23', 10),
(3865, 214, 71, NULL, NULL, '0000-00-00 00:00:00', 0, '2018-06-30 12:58:09', 10),
(3188, 181, 83, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:57:00', 10),
(1579, 42, 6, NULL, NULL, '2016-02-13 15:19:23', 10, '2016-02-13 15:19:23', 10),
(3198, 196, 75, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:59:23', 10),
(3197, 196, 22, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:59:23', 10),
(3187, 181, 75, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:57:00', 10),
(2069, 41, 16, NULL, NULL, '2017-04-10 17:04:40', 10, '2017-04-10 17:04:40', 10),
(3196, 196, 3, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:59:23', 10),
(3864, 214, 22, NULL, NULL, '0000-00-00 00:00:00', 0, '2018-06-30 12:58:09', 10),
(3186, 181, 22, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:57:00', 10),
(2070, 41, 3, NULL, NULL, '2017-04-10 17:04:40', 10, '2017-04-10 17:04:40', 10),
(3185, 181, 3, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:57:00', 10),
(3184, 181, 20, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:57:00', 10),
(3183, 181, 16, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:57:00', 10),
(3195, 196, 16, NULL, NULL, '0000-00-00 00:00:00', 0, '2017-06-16 11:59:23', 10),
(2068, 41, 7, NULL, NULL, '2017-04-10 17:04:40', 10, '2017-04-10 17:04:40', 10),
(2067, 41, 6, NULL, NULL, '2017-04-10 17:04:40', 10, '2017-04-10 17:04:40', 10),
(3863, 214, 3, NULL, NULL, '0000-00-00 00:00:00', 0, '2018-06-30 12:58:09', 10),
(3862, 214, 1, NULL, NULL, '0000-00-00 00:00:00', 0, '2018-06-30 12:58:09', 10),
(6125, 41, 3, NULL, NULL, NULL, NULL, '2019-05-11 00:00:00', 1),
(6126, 42, 3, NULL, NULL, NULL, NULL, '2019-05-11 00:00:00', 1),
(6148, 112, 3, NULL, NULL, NULL, NULL, '2019-05-11 00:00:00', 1),
(6163, 196, 3, NULL, NULL, NULL, NULL, '2019-05-11 00:00:00', 1),
(6165, 181, 3, NULL, NULL, NULL, NULL, '2019-05-11 00:00:00', 1),
(6178, 214, 3, NULL, NULL, NULL, NULL, '2019-05-11 00:00:00', 1);

同样的 fiddle ......

https://www.db-fiddle.com/f/22WF9LtaUKjzzU6d9S4ALH/3

Answer 1

这样做会:

SELECT r.id, r.name 
FROM recipes r 
JOIN recipe_categories rc ON r.id = rc.recipe_id 
WHERE rc.recipe_category_name_id ='2' OR rc.recipe_category_name_id ='3'
GROUP BY r.id, r.name 
HAVING COUNT(DISTINCT rc.recipe_category_name_id) = 2 

参见 demo .
结果:

| id  | name             |
| --- | ---------------- |
| 22  | sugar-free tarts |