I'm trying to solve a puzzle but can't get my head around it. The solution should preferably be in DB2, but other SQL variants are also acceptable.
Let's say we have this table listing owners and their cars (each owner can have more than one car):
car_owners:
Owner | Make | Model | Address | Phone |
---|---|---|---|---|
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 |
And then we have this table with car features and their codes:
car_features_codes:
Code | Feature |
---|---|
1 | sedan |
2 | not sedan |
3 | ABS |
4 | no ABS |
5 | automatic |
6 | not automatic |
7 | gasoline |
8 | no gasoline |
Note that this table has 4 features each with a "yes" or "no" code, thus having a total of 8 codes, and these codes are sequential numbers.
For better visualization, the above table could be presented like this:
Feature | Yes | No |
---|---|---|
Sedan | 1 | 2 |
ABS | 3 | 4 |
Automatic | 5 | 6 |
Gasoline | 7 | 8 |
And, finally, we have this table of car features:
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 |
Note that of all the 4 features, the Mustang and the Porsche has all of them, the Prius has only 2, and the other cars don't have any.
Now, the challenge is to fill in the last table with all the features of all the cars of the first table, using the "negative" codes of the second table as default. For example: if a car has no code 7 nor 8, then fill in with 8 (no gasoline). If 5 or 6 are missing, fill in with 6. If 1 or 2 are missing, fill in with 2. Etc.
The final "car_features" table should have a total of X*4
rows (X being the number of cars of the first table, each car having all the 4 features), preserving the rows that are already in the table.
The final table should look like this:
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 |
3 | Toyota | Prius | 4 |
3 | Toyota | Prius | 8 |
1 | Toyota | Corolla | 2 |
1 | Toyota | Corolla | 4 |
1 | Toyota | Corolla | 6 |
1 | Toyota | Corolla | 8 |
2 | Toyota | Corolla | 2 |
2 | Toyota | Corolla | 4 |
2 | Toyota | Corolla | 6 |
2 | Toyota | Corolla | 8 |
2 | Audi | A7 | 2 |
2 | Audi | A7 | 4 |
2 | Audi | A7 | 6 |
2 | Audi | A7 | 8 |
Note: the first 10 rows are the same as before. Then I completed with the other features missing for all cars, each with the "negative" option by default, like, if there's no ABS indicator (no "yes" or "no" codes), then I fill with "no ABS" ("no" code), and repeat for all the other features.
I have tried multiple queries using the INSERT statement, but can't figure out how to do it.
To generate rows that do not exist a CROSS JOIN
(Cartesian product) of 2 sets can be used. Here we need the existing car owners cross joined to negative features. This ensures that all owners and all negative codes exist in the result.
As there is a postive and matching negative feature we need to ensure that if either of a pair exist in the current table that we don't negate the existing positive or vice-versa. So, we left join that result to the existing car owner information to identify any missing rows, but also check that the data does not already exist using not exists:
INSERT INTO car_features (Owner, Make, Model, Feature)
WITH all_combinations AS (
SELECT o.Owner, o.Make, o.Model, c.Code as Feature
FROM car_owners o
CROSS JOIN car_features_codes c
WHERE c.Code IN (2,4,6,8)
),
existing_features AS (
SELECT o.Owner, o.Make, o.Model, f.Feature, CASE WHEN f.Feature % 2 = 0 THEN f.Feature - 1 ELSE f.Feature + 1 END as OppositeFeature
FROM car_owners o
JOIN car_features f ON o.Owner = f.Owner AND o.Make = f.Make AND o.Model = f.Model
)
SELECT ac.Owner, ac.Make, ac.Model, ac.Feature
FROM all_combinations ac
WHERE NOT EXISTS (
SELECT 1
FROM existing_features ef
WHERE ac.Owner = ef.Owner
AND ac.Make = ef.Make
AND ac.Model = ef.Model
AND (ac.Feature = ef.Feature OR ac.Feature = ef.OppositeFeature)
)
rows after the insets:
+-------+---------+---------+---------+
| OWNER | MAKE | MODEL | FEATURE |
+-------+---------+---------+---------+
| 1 | Ford | Mustang | 2 |
| 1 | Ford | Mustang | 3 |
| 1 | Ford | Mustang | 6 |
| 1 | Ford | Mustang | 7 |
| 1 | Toyota | Corolla | 2 |
| 1 | Toyota | Corolla | 4 |
| 1 | Toyota | Corolla | 6 |
| 1 | Toyota | Corolla | 8 |
| 2 | Audi | A7 | 2 |
| 2 | Audi | A7 | 4 |
| 2 | Audi | A7 | 6 |
| 2 | Audi | A7 | 8 |
| 2 | Porsche | 911 | 2 |
| 2 | Porsche | 911 | 3 |
| 2 | Porsche | 911 | 5 |
| 2 | Porsche | 911 | 7 |
| 2 | Toyota | Corolla | 2 |
| 2 | Toyota | Corolla | 4 |
| 2 | Toyota | Corolla | 6 |
| 2 | Toyota | Corolla | 8 |
| 3 | Toyota | Prius | 1 |
| 3 | Toyota | Prius | 4 |
| 3 | Toyota | Prius | 5 |
| 3 | Toyota | Prius | 8 |
+-------+---------+---------+---------+
See DB2 fiddle
nb this solution assumes the 8 codes are in pairs and that odd integers are the positives and "+1" will be the matching negative. If this is not true, then you may need to provide a "mapping table" of positive to negative of each basic feature.