Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE car_owners (
    Owner INT,
    Make VARCHAR(50),
    Model VARCHAR(50),
    Address VARCHAR(100),
    Phone VARCHAR(20)
);
CREATE TABLE car_features_codes (
    Code INT,
    Feature VARCHAR(50)
);
CREATE TABLE car_features (
    Owner INT,
    Make VARCHAR(50),
    Model VARCHAR(50),
    Feature INT
);
INSERT INTO car_owners
VALUES
(1, 'Toyota', 'Corolla', 'first street', '11111'),
(1, 'Ford', 'Mustang', 'first street', '11111'),
(2, 'Toyota', 'Corolla', 'second street', '22222'),
(2, 'Audi', 'A7', 'second street', '22222'),
(2, 'Porsche', '911', 'second street', '22222'),
(3, 'Toyota', 'Prius', 'third street', '33333');
INSERT INTO car_features_codes
VALUES
(1, 'sedan'),
(2, 'not sedan'),
(3, 'ABS'),
(4, 'no ABS'),
(5, 'automatic'),
(6, 'not automatic'),
(7, 'gasoline'),
(8, 'no gasoline');
INSERT INTO car_features
VALUES
(1, 'Ford', 'Mustang', 2),
(1, 'Ford', 'Mustang', 3),
(1, 'Ford', 'Mustang', 6),
(1, 'Ford', 'Mustang', 7),
(3, 'Toyota', 'Prius', 1),
(3, 'Toyota', 'Prius', 5),
(2, 'Porsche', '911', 2),
(2, 'Porsche', '911', 3),
(2, 'Porsche', '911', 5),
(2, 'Porsche', '911', 7);
WITH temp AS (
    SELECT o.Owner, o.Make, o.Model, c.Code as Feature
    FROM car_owners o
    CROSS JOIN car_features_codes c
    WHERE c.feature LIKE 'no%'
)
SELECT t.Owner, t.Make, t.Model, t.Feature
FROM temp t
LEFT JOIN car_features f
ON t.Owner = f.Owner AND t.Make = f.Make AND t.Model = f.Model AND t.Feature = f.Feature
WHERE f.Feature IS NULL


OWNER
MAKE
MODEL
FEATURE


2
Audi
A7
8

2
Porsche
911
8

2
Audi
A7
4

2
Audi
A7
6

2
Toyota
Corolla
8

2
Audi
A7
2

2
Porsche
911
6

2
Toyota
Corolla
6

2
Toyota
Corolla
4

2
Porsche
911
4

3
Toyota
Prius
4

2
Toyota
Corolla
2

3
Toyota
Prius
8

1
Toyota
Corolla
2

1
Ford
Mustang
8

1
Toyota
Corolla
6

1
Toyota
Corolla
4

1
Ford
Mustang
4

3
Toyota
Prius
2

1
Toyota
Corolla
8

3
Toyota
Prius
6


INSERT INTO car_features (Owner, Make, Model, Feature)
WITH temp AS (
    SELECT o.Owner, o.Make, o.Model, c.Code as Feature
    FROM car_owners o
    CROSS JOIN car_features_codes c
    WHERE c.feature LIKE 'no%'
)
SELECT t.Owner, t.Make, t.Model, t.Feature
FROM temp t
LEFT JOIN car_features f
ON t.Owner = f.Owner AND t.Make = f.Make AND t.Model = f.Model AND t.Feature = f.Feature
WHERE f.Feature IS NULL
SELECT
  * 
FROM  car_features


OWNER
MAKE
MODEL
FEATURE


1
Ford
Mustang
2

1
Ford
Mustang
3

1
Ford
Mustang
6

1
Ford
Mustang
7

3
Toyota
Prius
1

3
Toyota
Prius
5

2
Porsche
911
2

2
Porsche
911
3

2
Porsche
911
5

2
Porsche
911
7

1
Toyota
Corolla
2

2
Toyota
Corolla
2

2
Audi
A7
2

3
Toyota
Prius
2

1
Toyota
Corolla
4

1
Ford
Mustang
4

2
Toyota
Corolla
4

2
Audi
A7
4

2
Porsche
911
4

3
Toyota
Prius
4

1
Toyota
Corolla
6

2
Toyota
Corolla
6

2
Audi
A7
6

2
Porsche
911
6

3
Toyota
Prius
6

1
Toyota
Corolla
8

1
Ford
Mustang
8

2
Toyota
Corolla
8

2
Audi
A7
8

2
Porsche
911
8

3
Toyota
Prius
8

OWNER	MAKE	MODEL	FEATURE
2	Audi	A7	8
2	Porsche	911	8
2	Audi	A7	4
2	Audi	A7	6
2	Toyota	Corolla	8
2	Audi	A7	2
2	Porsche	911	6
2	Toyota	Corolla	6
2	Toyota	Corolla	4
2	Porsche	911	4
3	Toyota	Prius	4
2	Toyota	Corolla	2
3	Toyota	Prius	8
1	Toyota	Corolla	2
1	Ford	Mustang	8
1	Toyota	Corolla	6
1	Toyota	Corolla	4
1	Ford	Mustang	4
3	Toyota	Prius	2
1	Toyota	Corolla	8
3	Toyota	Prius	6