mysql - sql多表查询

我有3个相关的表，我想用查询sql显示一个表，其中表“ recetas”的“ titulo”与表“ categorias”的“ categoria”一起被选择，一个配方可以有多个我已经创建了表“ recetas-categorias”的类别，我尝试了子查询，但我不明白，我是sql的初学者。

SELECT r.titulo
 FROM recetas r
WHERE r.idreceta=(SELECT rc.idreceta
                   FROM recetas-categorias
                  WHERE rc.idcategoria=(SELECT id
                                         FROM categorias c
                                        WHERE c.categoria="Internacional"));

relationship of the three tables

categorias table

recetas-categorias table

recetas table

附加代码的结构和数据

Recetas表

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `recetas`;
CREATE TABLE IF NOT EXISTS `recetas` (
  `idreceta` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(255) COLLATE utf8_spanish2_ci NOT NULL,
  `contenido` mediumtext COLLATE utf8_spanish2_ci NOT NULL,
  `fecha` date NOT NULL,
  `idusuario` varchar(255) COLLATE utf8_spanish2_ci NOT NULL,
  `imagenprincipal` varchar(255) COLLATE utf8_spanish2_ci NOT NULL,
  `valoracion` float DEFAULT NULL,
  PRIMARY KEY (`idreceta`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;

INSERT INTO `recetas` (`idreceta`, `titulo`, `contenido`, `fecha`, `idusuario`, `imagenprincipal`, `valoracion`) VALUES
(1, 'Paella mixta', '<ul class=\"list-group\">\r\n  <li class=\"list-group-item\">Cras justo odio</li>\r\n  <li class=\"list-group-item\">Dapibus ac facilisis in</li>\r\n  <li class=\"list-group-item\">Morbi leo risus</li>\r\n  <li class=\"list-group-item\">Porta ac consectetur ac</li>\r\n  <li class=\"list-group-item\">Vestibulum at eros</li>\r\n</ul>', '2019-01-15', '1', 'paella.jpg', NULL),
(2, 'Boquerones', '<ul class=\"list-group\">\r\n  <li class=\"list-group-item\">Cras justo odio</li>\r\n  <li class=\"list-group-item\">Dapibus ac facilisis in</li>\r\n  <li class=\"list-group-item\">Morbi leo risus</li>\r\n  <li class=\"list-group-item\">Porta ac consectetur ac</li>\r\n  <li class=\"list-group-item\">Vestibulum at eros</li>\r\n</ul>', '2019-01-24', '1', 'boquerones.jpg', NULL),
(3, 'Salchichas', '<ul class=\"list-group\">\r\n  <li class=\"list-group-item\">Cras justo odio</li>\r\n  <li class=\"list-group-item\">Dapibus ac facilisis in</li>\r\n  <li class=\"list-group-item\">Morbi leo risus</li>\r\n  <li class=\"list-group-item\">Porta ac consectetur ac</li>\r\n  <li class=\"list-group-item\">Vestibulum at eros</li>\r\n</ul>', '2019-01-24', '1', 'salchi.jpg', NULL);
COMMIT;

recetas-categorias表

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `recetas-categorias`;
CREATE TABLE IF NOT EXISTS `recetas-categorias` (
  `idreceta` int(11) NOT NULL,
  `idcategoria` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;

INSERT INTO `recetas-categorias` (`idreceta`, `idcategoria`) VALUES
(1, 6),
(1, 14),
(3, 9),
(3, 14),
(2, 10);
COMMIT;

类别表

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `categorias`;
CREATE TABLE IF NOT EXISTS `categorias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categoria` varchar(255) COLLATE utf8_spanish2_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;

INSERT INTO `categorias` (`id`, `categoria`) VALUES
(1, 'Sopas y cremas'),
(2, 'Salsas'),
(3, 'Potajes'),
(4, 'Ensaladas'),
(5, 'Pastas'),
(6, 'Arroces'),
(7, 'Legumbres'),
(8, 'Huevos'),
(9, 'Carnes'),
(10, 'Pescados'),
(11, 'Mariscos'),
(12, 'Cocteles y refrescos'),
(13, 'Postres y repostería'),
(14, 'Internacional'),
(15, 'Robot de cocina');
COMMIT;

最佳答案

我认为，从了解您的问题开始，您可能希望将其引入MySQL表联接。

它是一个广泛的类别，在这里无法回答，因此最好将您引导至http://www.mysqltutorial.org/mysql-join/之类的地方，以了解它们的用途以及如何使用它们。

根据您的查询，进行以下修改：

SELECT r.titulo
 FROM `categorias` c
INNER JOIN `recetas-categorias` rc ON rc.idcategoria= c.id
INNER JOIN `recetas` r ON r.idreceta = rc.idreceta
WHERE c.categoria="Internacional"

退货

+-------------+
| paella      |
|-------------|
| salchichas  |
+-------------+

...这似乎是您要达到的目标？

关于mysql - sql多表查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54204810/