MYSQL full join 4 tables using a reference table

user_2889265 0
Viewed 53

I need some help building a SQL to fetch something like a "FULL OUTER JOIN" over four tables. I have this structure and cannot really modify much on it, cause its a already in use database:

enter image description here

-- ----------------------------
-- 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

What i want to get is a new VIEW which contains a join over all entity-tables showing a row for all articles, all properties and all languages but using the already existing data if available or null if not.

enter image description here

Is it possible? How would the SQL look like?

mysql sql
2 Answers

You seem to be after this...

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               |
+-----------+------------+------------+--------------------+
user_15296730

If you want all articles, you don't want a "full join". You want a left join that starts with the articles table. Further, you don't even need that, because all your articles have values in data.

But the question does specify all articles, so:

SELECT a.*, p.*, l.*, d.*
FROM article a LEFT JOIN
     data d 
     ON d.ArticleID = a.ID LEFT JOIN
     property p
     ON d.PropertyID = p.id LEFT JOIN
     language l
     ON d.LanguageID = l.id;

I'm not sure what you mean by all articles and all languages (I missed the second part when I first read the question). If you want all combinations then:

SELECT a.*, p.*, l.*, d.*
FROM article a CROSS JOIN
     languages l LEFT JOIN
     data d 
     ON d.ArticleID = a.ID AND
        d.LanguageID = l.ID LEFT JOIN
     property p
     ON d.PropertyID = p.id ;
user_11440350
Related