Hi all,
I’ve these two tables t1 and t2 stored an a database MySql version 8.0.17
It’s important to say that the table t2 can contain thousands of rows with different unit code (field sUn )…
Instead the table t1 contains only the how and code of each unit (field TABLE_NAME )
-- ----------------------------
-- Table structure for t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`TABLE_NAME` varchar(255) DEFAULT NULL,
`tDDMMYYHHMMSS` datetime DEFAULT NULL,
`sID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sID`) USING BTREE,
UNIQUE INDEX `TABLE_NAME`(`TABLE_NAME`) USING BTREE
) ENGINE = InnoDB;
-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES ('100', '2021-04-09 12:44:30', 1);
INSERT INTO `t1` VALUES ('11c', '2021-04-09 12:44:30', 2);
INSERT INTO `t1` VALUES ('11f', '2021-04-09 12:44:30', 3);
INSERT INTO `t1` VALUES ('12a', '2021-04-09 12:44:30', 4);
INSERT INTO `t1` VALUES ('12h', '2021-04-09 12:44:30', 5);
-- ----------------------------
-- Table structure for t2
-- ----------------------------
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sUn` varchar(255) DEFAULT NULL,
`sUnName` varchar(255) DEFAULT NULL,
`sContents` longtext NULL,
PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB;
-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO `t2` VALUES (1, '100', 'NOR', 'Ipsa sua melior fama.');
INSERT INTO `t2` VALUES (2, '100', 'NOR', 'In toto.');
INSERT INTO `t2` VALUES (3, '100', 'NOR', 'Homines, nihil agendo.');
INSERT INTO `t2` VALUES (4, '11C', 'SAR', 'Habere non haberi.');
INSERT INTO `t2` VALUES (5, '11C', 'SAR', 'Vivere est cogitare.');
INSERT INTO `t2` VALUES (6, '11C', 'SAR', 'Urbi et Orbi.');
INSERT INTO `t2` VALUES (7, '11F', 'SAD', 'Inter sidera versor.');
INSERT INTO `t2` VALUES (8, '11F', 'SAD', 'Una tantum.');
INSERT INTO `t2` VALUES (9, '11F', 'SAD', 'Carthago delenda est.');
INSERT INTO `t2` VALUES (10, '12A', 'RIV', 'Status quo.');
INSERT INTO `t2` VALUES (11, '12A', 'RIV', 'Aut aut.');
INSERT INTO `t2` VALUES (12, '12A', 'RIV', 'Condicio sine qua non.\r\n');
INSERT INTO `t2` VALUES (13, '12H', 'CUN', 'Ubi maior minor cessat.');
INSERT INTO `t2` VALUES (14, '12H', 'CUN', 'Carpe diem.');
INSERT INTO `t2` VALUES (15, '12H', 'CUN', 'Venni, vidi, vinsi.');
I’ve tried - without success - one query pivot join these two tables for this return
+------------------------+----------------------+-----------------------+------------------------+-------------------------+
| NOR | SAR | SAD | RIV | CUN |
+------------------------+----------------------+-----------------------+------------------------+-------------------------+
| Ipsa sua melior fama. | Habere non haberi. | Inter sidera versor. | Status quo. | Ubi maior minor cessat. |
| In toto. | Vivere est cogitare. | Una tantum. | Aut aut. | Carpe diem. |
| Homines, nihil agendo. | Urbi et Orbi. | Carthago delenda est. | Condicio sine qua non. | Venni, vidi, vinsi. |
+------------------------+----------------------+-----------------------+------------------------+-------------------------+
My query below with error
Any help would greatly appreciate… Thank you.
SELECT
max(
IF
( t.sUn = q.TABLE_NAME, t.sContents, NULL )) q.sUnName
FROM
( SELECT row_number() over ( PARTITION BY sUn ORDER BY sID ) AS rn, tcs.sUn, tcs.sContents FROM t2 AS tcs ) AS t
JOIN t1 q ON t.sUn = q.TABLE_NAME
GROUP BY
rn
ORDER BY
rn
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘.sUnName FROM ( SELECT row_number() over ( PARTITION BY sUn ORDER BY sID ) AS’ at line 4 > Time: 0,001s
