By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS `T1`;
CREATE TABLE IF NOT EXISTS `T1` (
`id` smallint NOT NULL AUTO_INCREMENT,
`TypeID` smallint DEFAULT NULL,
`GroupName` varchar(16) NOT NULL DEFAULT 'N/A',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO `T1` (`id`,`TypeID`,`GroupName`) VALUES
(1,1,'GName01'),
(2,1,'GName02');
Records: 2 Duplicates: 0 Warnings: 0
SELECT *
FROM T1;
| id | TypeID | GroupName |
|---|---|---|
| 1 | 1 | GName01 |
| 2 | 1 | GName02 |
DROP TABLE IF EXISTS `T2`;
CREATE TABLE IF NOT EXISTS `T2` (
`id` smallint NOT NULL AUTO_INCREMENT,
`TypeID` smallint DEFAULT NULL,
`GroupID` smallint NOT NULL DEFAULT '0',
`PersonID` smallint DEFAULT NULL,
`PersonName` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO `T2` (`id`,`TypeID`,`GroupID`,`PersonID`,`PersonName`) VALUES
(1,1,1,1,'Name01'),
(2,1,1,2,'Name02'),
(3,1,1,3,'Name03'),
(4,1,1,4,'Name04'),
(5,1,2,1,'Name05'),
(6,1,2,2,'Name06'),
(7,1,2,3,'Name07'),
(8,1,2,4,'Name08');
Records: 8 Duplicates: 0 Warnings: 0
SELECT *
FROM T2;
| id | TypeID | GroupID | PersonID | PersonName |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | Name01 |
| 2 | 1 | 1 | 2 | Name02 |
| 3 | 1 | 1 | 3 | Name03 |
| 4 | 1 | 1 | 4 | Name04 |
| 5 | 1 | 2 | 1 | Name05 |
| 6 | 1 | 2 | 2 | Name06 |
| 7 | 1 | 2 | 3 | Name07 |
| 8 | 1 | 2 | 4 | Name08 |
DROP TABLE IF EXISTS `T3`;
CREATE TABLE IF NOT EXISTS `T3` (
`id` smallint NOT NULL AUTO_INCREMENT,
`TypeID` smallint DEFAULT NULL,
`GroupID` smallint NOT NULL DEFAULT '0',
`Element1ID` smallint DEFAULT NULL,
`Element2ID` smallint DEFAULT NULL,
`Element3ID` smallint DEFAULT NULL,
`Element1Name` varchar(16) DEFAULT NULL,
`Element2Name` varchar(16) DEFAULT NULL,
`Element3Name` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO `T3` (`id`,`TypeID`,`GroupID`,`Element1ID`,`Element2ID`,`Element3ID`,`Element1Name`,`Element2Name`,`Element3Name`) VALUES
(1,1,1,NULL,NULL,NULL,NULL,NULL,NULL),
(2,1,1,NULL,NULL,NULL,NULL,NULL,NULL),
(3,1,1,1,NULL,NULL,'Name01',NULL,NULL),
(4,1,1,1,2,NULL,'Name01','Name02',NULL),
(5,1,1,2,3,4,'Name02','Name03','Name04'),
(6,1,1,2,4,NULL,'Name02','Name04',NULL),
(7,1,1,1,3,4,'Name01','Name03','Name04'),
(8,1,1,4,NULL,NULL,'Name04',NULL,NULL),
(9,1,2,NULL,NULL,NULL,NULL,NULL,NULL),
(10,1,2,3,4,NULL,'Name07','Name08',NULL),
(11,1,2,3,NULL,NULL,'Name07',NULL,NULL);
Records: 11 Duplicates: 0 Warnings: 0
SELECT *
FROM T3;
| id | TypeID | GroupID | Element1ID | Element2ID | Element3ID | Element1Name | Element2Name | Element3Name |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | null | null | null | null | null | null |
| 2 | 1 | 1 | null | null | null | null | null | null |
| 3 | 1 | 1 | 1 | null | null | Name01 | null | null |
| 4 | 1 | 1 | 1 | 2 | null | Name01 | Name02 | null |
| 5 | 1 | 1 | 2 | 3 | 4 | Name02 | Name03 | Name04 |
| 6 | 1 | 1 | 2 | 4 | null | Name02 | Name04 | null |
| 7 | 1 | 1 | 1 | 3 | 4 | Name01 | Name03 | Name04 |
| 8 | 1 | 1 | 4 | null | null | Name04 | null | null |
| 9 | 1 | 2 | null | null | null | null | null | null |
| 10 | 1 | 2 | 3 | 4 | null | Name07 | Name08 | null |
| 11 | 1 | 2 | 3 | null | null | Name07 | null | null |
/*
how many "hits" get members/persons in each group
|TypeID|GroupID |PersonID |PersonName |Hits |
|1 |1 |1 |Name01 |3 |
|1 |1 |2 |Name02 |3 |
|1 |1 |2 |Name03 |2 |
|1 |1 |4 |Name04 |4 |
|1 |2 |3 |Name07 |2 |
|1 |2 |4 |Name08 |1 |
*/
SELECT ID, TypeID, GroupID, Element1ID AS PersonID, Element1Name AS PersonName, 1 AS Element FROM T3 WHERE Element1ID IS NOT NULL
UNION ALL
SELECT ID, TypeID, GroupID, Element2ID AS PersonID, Element2Name AS PersonName, 2 AS Element FROM T3 WHERE Element2ID IS NOT NULL
UNION ALL
SELECT ID, TypeID, GroupID, Element3ID AS PersonID, Element3Name AS PersonName, 3 AS Element FROM T3 WHERE Element3ID IS NOT NULL;
| ID | TypeID | GroupID | PersonID | PersonName | Element |
|---|---|---|---|---|---|
| 3 | 1 | 1 | 1 | Name01 | 1 |
| 4 | 1 | 1 | 1 | Name01 | 1 |
| 5 | 1 | 1 | 2 | Name02 | 1 |
| 6 | 1 | 1 | 2 | Name02 | 1 |
| 7 | 1 | 1 | 1 | Name01 | 1 |
| 8 | 1 | 1 | 4 | Name04 | 1 |
| 10 | 1 | 2 | 3 | Name07 | 1 |
| 11 | 1 | 2 | 3 | Name07 | 1 |
| 4 | 1 | 1 | 2 | Name02 | 2 |
| 5 | 1 | 1 | 3 | Name03 | 2 |
| 6 | 1 | 1 | 4 | Name04 | 2 |
| 7 | 1 | 1 | 3 | Name03 | 2 |
| 10 | 1 | 2 | 4 | Name08 | 2 |
| 5 | 1 | 1 | 4 | Name04 | 3 |
| 7 | 1 | 1 | 4 | Name04 | 3 |
WITH T4 AS
(
SELECT ID, TypeID, GroupID, Element1ID AS PersonID, Element1Name AS PersonName, 1 AS Element FROM T3 WHERE Element1ID IS NOT NULL
UNION ALL
SELECT ID, TypeID, GroupID, Element2ID AS PersonID, Element2Name AS PersonName, 2 AS Element FROM T3 WHERE Element2ID IS NOT NULL
UNION ALL
SELECT ID, TypeID, GroupID, Element3ID AS PersonID, Element3Name AS PersonName, 3 AS Element FROM T3 WHERE Element3ID IS NOT NULL
)
SELECT TypeID, GroupID, PersonID, PersonName, COUNT(*) AS Hits
FROM T4
GROUP BY TypeID, GroupID, PersonID, PersonName
ORDER BY TypeID, GroupID, PersonID;
| TypeID | GroupID | PersonID | PersonName | Hits |
|---|---|---|---|---|
| 1 | 1 | 1 | Name01 | 3 |
| 1 | 1 | 2 | Name02 | 3 |
| 1 | 1 | 3 | Name03 | 2 |
| 1 | 1 | 4 | Name04 | 4 |
| 1 | 2 | 3 | Name07 | 2 |
| 1 | 2 | 4 | Name08 | 1 |
/*
how many "hits" get each group
|TypeID |GroupID|GroupName|Hits |
|1 |1 |GName01 |8 |
|1 |2 |GName02 |3 |
*/