Make sure records from one table always overwrite others when joining

Question

Here's a Fiddle example

I want to combine five EAV tables into a big table which stores all product attributes. Let's say there are two phones A and B from the same series called Nokia 1150. Both phones share some identical information like price,brand,ram. The series use green plastic cases. But B phone is a special edition. Its case is made of gold and is black. I want to pivot the tables and use IFNULL or coalesce to make sure that table model_attr always overwrites series_attr

I want the output to be like this:

MODEL_NAME  SERIES_NAME     PRICE   RAM BRAND   MATERIAL    COLOR
A           Nokia Series    5000    512 Nokia   Plastic     Green
B           Nokia Series    5000    512 Nokia   Gold        Black

My Code:

Select m.model_name,s.series_name,s.price,s.ram,s.brand,
    GROUP_CONCAT(
     IF(a.attr_name = 'material',a.attr_value, NULL)) AS material,
    GROUP_CONCAT(
     IF(a.attr_name = 'color',a.attr_value, NULL)) AS color
FROM model m INNER JOIN series s 
LEFT JOIN series_attr sa ON sa.series_id = s.series_id
LEFT JOIN model_attr ma ON ma.model_id = m.model_id
LEFT JOIN attr a ON a.attr_id = ma.attr_id
GROUP BY m.model_name

What I'm getting is this:

MODEL_NAME  SERIES_NAME     PRICE   RAM BRAND   MATERIAL    COLOR
A           Nokia Series    5000    512 Nokia   (null)  (null)
B           Nokia Series    5000    512 Nokia   Gold,Gold,Gold,Gold Black,Black,Black,Black

Table Schema

 CREATE TABLE series
        (`series_id` int, `series_name` varchar(20),`price` int,`ram`int,`brand`varchar(20))
    ;

INSERT INTO series
    (`series_id`,`series_name`,`price`,`ram`,`brand`)
VALUES
    (1,'Nokia Series',5000,512,'Nokia'),
    (2,'Sony Series',2500,1024,'Sony')

;

CREATE TABLE model
    (`model_id` int, `model_name` varchar(20),`series_id` int)
;

INSERT INTO model
    (`model_id`,`model_name`,`series_id`)
VALUES
    (1,'A',1),
    (2, 'B',1),
    (3, 'C',2)
;

CREATE TABLE attr
    (`attr_id` int, `attr_name` varchar(20),`attr_value` varchar(20))
;

INSERT INTO attr
    (`attr_id`,`attr_name`,`attr_value`)
VALUES
    (1, 'material','Gold'),
    (2, 'material','Plastic'),
    (3, 'color','Grey'),
    (4, 'color','Black'),
    (5, 'color','Green')


;

CREATE TABLE series_attr
    (`series_id` int, `attr_id` int )
;
INSERT INTO series_attr
    (`series_id`,`attr_id`)
VALUES
    (1,2),
    (1,5),
    (2,2),
    (2,3)
;



CREATE TABLE model_attr
    (`model_id` int, `attr_id` int,`series_group`int )
;
INSERT INTO model_attr
    (`model_id`,`attr_id`,`series_group`)
VALUES
    (2,1,1),
    (2,4,1)
;

I've create a field called series_group in table model_attr. I hope that would make joining easier. Any help would be appreciate

Answer 1

You actually did the bulk of the work. The key change, though, is to join in the attribute table twice, once for the series and once for the models. Then you can look at the attributes from each separately and choose the model, if they exist, or then the series:

Select m.model_name, s.series_name, s.price, s.ram, s.brand,
       (case when sum(a.attr_name = 'material') > 0
             then GROUP_CONCAT(DISTINCT IF(a.attr_name = 'material', a.attr_value, NULL))
             else GROUP_CONCAT(DISTINCT IF(saa.attr_name = 'material', saa.attr_value, NULL))
        end) AS material,
       (case when sum(a.attr_name = 'color') > 0
             then GROUP_CONCAT(DISTINCT IF(a.attr_name = 'color', a.attr_value, NULL))
             else GROUP_CONCAT(DISTINCT IF(saa.attr_name = 'color', saa.attr_value, NULL))
        end) AS color
FROM model m INNER JOIN
     series s
     on m.series_id = s.series_id LEFT JOIN
     series_attr sa
     ON sa.series_id = s.series_id LEFT JOIN
     attr saa
     on saa.attr_id = sa.attr_id LEFT JOIN
     model_attr ma
     ON ma.model_id = m.model_id LEFT JOIN
     attr a
     ON a.attr_id = ma.attr_id
GROUP BY m.model_name;