i906 i906 - 1 month ago 6
MySQL Question

How to override a row value by using a value from another row originated from another table?

I have a few tables as follows:

product_sets


ID | Value
---------------
A | original_a
B | original_b
C | original_c
D | original_d


override_sets


ID | Name
----------
X | Set 1
Y | Set 2


override_entries


ID | Set ID | Product ID | Override Value
-----------------------------------------
1 | X | A | new_a
2 | X | B | new_b
3 | Y | A | custom_a


I already tried using several joins but I'll get like below:

Product ID | Set ID | Final Value
---------------------------------
A | X | new_a
B | X | new_b
A | Y | custom_a


I want to have a SQL view
final_products
that produces a result like below:

Product ID | Set ID | Final Value
---------------------------------
A | X | new_a
B | X | new_b
C | X | original_c
D | X | original_d
A | Y | custom_a
B | Y | original_b
C | Y | original_c
D | Y | original_d


SQL Statements:

CREATE TABLE `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `override_sets` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `override_entries` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`set_id` int(10) unsigned NOT NULL,
`product_id` int(10) unsigned NOT NULL,
`value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `override_entries_set_id_index` (`set_id`),
KEY `override_entries_product_id_index` (`product_id`),
CONSTRAINT `override_entries_set_id_foreign` FOREIGN KEY (`set_id`) REFERENCES `override_sets` (`id`) ON DELETE CASCADE,
CONSTRAINT `override_entries_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
);

CREATE VIEW `final_products` AS
SELECT
p.id,
e.set_id,
CASE
WHEN e.value IS NULL THEN p.value
ELSE e.value
END AS final_value
FROM products AS p
LEFT OUTER JOIN override_entries AS e ON p.id = e.product_id;


Data sets:

INSERT INTO `products` (`id`, `value`)
VALUES
(1,'original_a'),
(2,'original_b'),
(3,'original_c'),
(4,'original_d');

INSERT INTO `override_sets` (`id`, `name`)
VALUES
(1,'Set 1'),
(2,'Set 2');

INSERT INTO `override_entries` (`id`, `set_id`, `product_id`, `value`)
VALUES
(1,1,1,'new_a'),
(2,1,2,'new_b'),
(3,2,1,'custom_a');

Answer

Using definition and dataset given, below method uses combination of cross join (for joining tables with no relation) and a left join;

select a.id as `Product ID`, b.id as `Set Id` ,
case when c.value is null then a.value else c.value end as `Final Value`
from products a 
cross join override_sets b
left join override_entries c on a.id = c.product_id and b.id = c.set_id
order by b.id, a.id;
Comments