这是我的简化数据库方案
-- -----------------------------------------------------
-- Table `products`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
`id` INT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `properties`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `properties` (
`id` INT NOT NULL AUTO_INCREMENT,
`product_id` INT NULL,
`key` VARCHAR(45) NULL,
`value` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Data for table `products`
-- -----------------------------------------------------
INSERT INTO `products` (`id`, `name`) VALUES (1, 'English Book with AudioCD');
INSERT INTO `products` (`id`, `name`) VALUES (2, 'Polish Book');
-- -----------------------------------------------------
-- Data for table `properties`
-- -----------------------------------------------------
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (1, 1, 'Format', 'Book');
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (2, 1, 'Format', 'Audio');
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (3, 2, 'Format', 'Book');
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (4, 1, 'Language', 'English');
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (5, 2, 'Language', 'Polish');
这里有表格表示
+----+--------------------------+
| id | name |
+----+--------------------------+
| 1 | English Book wit AudioCD |
| 2 | Polish Book |
+----+--------------------------+
+----+------------+----------+---------+
| id | product_id | key | value |
+----+------------+----------+---------+
| 1 | 1 | Format | Book |
| 2 | 1 | Format | AudioCD |
| 3 | 2 | Format | Book |
| 4 | 1 | Language | English |
| 5 | 2 | Language | Polish |
+----+------------+----------+---------+
我需要选择所有具有键“格式”且值为“Book”和“AudioCD”的产品.但我只需要满足所有这些条件的产品.所以我只想要product_id 1而不是2(因为它没有AudioCD).另外我想要也可以添加语言键.
没有多个JOINS,有没有办法做到这一点?
多个JOINS的问题开始于我可以说20-30个键值属性的组合,因为它们将被自由添加.
感谢任何提示!