MYSQL full join 4表使用引用表

Question

我需要一些帮助来构建一个 SQL 来获取诸如四个表的“FULL OUTER JOIN”之类的东西。我有这个结构，不能真正修改太多，因为它是一个已经在使用的数据库:

-- ----------------------------
-- Table structure for article
-- ----------------------------
CREATE TABLE `article`  (
  `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3;
INSERT INTO `article` VALUES (1, 'Coffeemaker');
INSERT INTO `article` VALUES (2, 'Toaster');

-- ----------------------------
-- Table structure for language
-- ----------------------------
CREATE TABLE `language`  (
  `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3;
INSERT INTO `language` VALUES (1, 'German');
INSERT INTO `language` VALUES (2, 'English');

-- ----------------------------
-- Table structure for property
-- ----------------------------
CREATE TABLE `property`  (
  `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3;
INSERT INTO `property` VALUES (1, 'DescriptionText');
INSERT INTO `property` VALUES (2, 'EAN-Code');

-- ----------------------------
-- Table structure for data
-- ----------------------------
CREATE TABLE `data`  (
  `ArticleID` int(10) UNSIGNED NOT NULL,
  `PropertyID` int(10) UNSIGNED NOT NULL,
  `LanguageID` int(10) UNSIGNED NOT NULL,
  `Value` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`ArticleID`, `PropertyID`, `LanguageID`) USING BTREE,
  CONSTRAINT `FK_ArticleID` FOREIGN KEY (`ArticleID`) REFERENCES `article` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_LanguageID` FOREIGN KEY (`LanguageID`) REFERENCES `language` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_PropertyID` FOREIGN KEY (`PropertyID`) REFERENCES `property` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

INSERT INTO `data` VALUES (1, 1, 1, 'Eine Kaffemaschine');
INSERT INTO `data` VALUES (2, 1, 2, 'A toaster');

SQL: http://sqlfiddle.com/#!9/91dc8/1

我想要得到的是一个新的 VIEW，它包含对所有实体表的连接，显示所有文章、所有属性和所有语言的一行，但如果可用则使用现有数据，如果不可用则使用 null。

这可能吗？ SQL 会是什么样子？

Answer 1

你似乎在追求这个......

SELECT a.id articleid
     , p.id propertyid
     , l.id languageid
     , d.value 
  FROM article a
 CROSS -- optional keyword
  JOIN property p
 CROSS -- optional keyword
  JOIN language l 
  LEFT -- not optional
  JOIN data d
    ON d.articleid = a.id
   AND d.propertyid = p.id
   AND d.languageid = l.id;

+-----------+------------+------------+--------------------+
| articleid | propertyid | languageid | value              |
+-----------+------------+------------+--------------------+
|         1 |          1 |          1 | Eine Kaffemaschine |
|         2 |          1 |          1 | NULL               |
|         1 |          2 |          1 | NULL               |
|         2 |          2 |          1 | NULL               |
|         1 |          1 |          2 | NULL               |
|         2 |          1 |          2 | A toaster          |
|         1 |          2 |          2 | NULL               |
|         2 |          2 |          2 | NULL               |
+-----------+------------+------------+--------------------+