MySQL – Select Rows from Child Table and Join into Parent Table as Columns

MySQL

Good evening StackExchange DBA's. I've been asked to pull pull back every piece of information from on of our machines and export it. The problem in is, all of the values in question are spread between a few tables that have a column structure like (parent_id BIGINT, attribute VARCHAR(40), val INT).

Here's a simplified idea of what our database structure looks like.

create table parent (id BIGINT, name VARCHAR(45));
create table child (parent_id BIGINT, attribute VARCHAR(10), val INT);

INSERT INTO parent (id, name) VALUES (1, 'Adam');
INSERT INTO parent (id, name) VALUES (2, 'Brent');
INSERT INTO parent (id, name) VALUES (3, 'Cathy');

INSERT INTO child (parent_id, attribute, val) VALUES (1, 'height', 67); 
INSERT INTO child (parent_id, attribute, val) VALUES (1, 'weight', 185);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'height', 63);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'weight', 162);
INSERT INTO child (parent_id, attribute, val) VALUES (2, 'arms', 2);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'height', 71);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'weight', 179);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'arms', 2);
INSERT INTO child (parent_id, attribute, val) VALUES (3, 'legs', 2);

Ideally, I'd like to return all the values and attributes of a parent in the same row but each parent will have vastly different attributes as it's children.

This is the closest I've come to what I'd like to see. Is there any improvement to be had in this query? Does anybody know what these types of queries are called so I know what to google?

select a.id, a.name, b.attr, b.val 
from parent a INNER JOIN 
  (select distinct parent_id, 
   GROUP_CONCAT(attribute) as attr, 
   GROUP_CONCAT(val) as val 
   from child group by parent_id) b
ON a.id = b.parent_id;

Thanks

Best Answer

Try this:

SELECT 
    parent.id, 
    parent.name, 
    GROUP_CONCAT(CONCAT(child.attribute, ":", child.val)) AS Attributes
FROM parent INNER JOIN child ON parent.id=child.parent_id
GROUP BY parent.id

Related Solutions

Product Attribute List Design Pattern in MySQL

I personally would use a model similar to the following:

The product table would be pretty basic, your main product details:

create table product
(
  part_number int, (PK)
  name varchar(10),
  price int
);
insert into product values
(1, 'product1', 50),
(2, 'product2', 95.99);

Second the attribute table to store the each of the different attributes.

create table attribute
(
  attributeid int, (PK)
  attribute_name varchar(10),
  attribute_value varchar(50)
);
insert into attribute values
(1, 'color', 'red'),
(2, 'color', 'blue'),
(3, 'material', 'chrome'),
(4, 'material', 'plastic'),
(5, 'color', 'yellow'),
(6, 'size', 'x-large');

Finally create the product_attribute table as the JOIN table between each product and its attributes associated with it.

create table product_attribute
(
  part_number int, (FK)
  attributeid int  (FK) 
);
insert into product_attribute values
(1,  1),
(1,  3),
(2,  6),
(2,  2),
(2,  6);

Depending on how you want to use the data you are looking at two joins:

select *
from product p
left join product_attribute t
  on p.part_number = t.part_number
left join attribute a
  on t.attributeid = a.attributeid;

See SQL Fiddle with Demo. This returns data in the format:

PART_NUMBER | NAME       | PRICE | ATTRIBUTEID | ATTRIBUTE_NAME | ATTRIBUTE_VALUE
___________________________________________________________________________
1           | product1   | 50    | 1           | color          | red
1           | product1   | 50    | 3           | material       | chrome
2           | product2   | 96    | 6           | size           | x-large
2           | product2   | 96    | 2           | color          | blue
2           | product2   | 96    | 6           | size           | x-large

But if you want to return the data in a PIVOT format where you have one row with all of the attributes as columns, you can use CASE statements with an aggregate:

SELECT p.part_number,
  p.name,
  p.price,
  MAX(IF(a.ATTRIBUTE_NAME = 'color', a.ATTRIBUTE_VALUE, null)) as color,
  MAX(IF(a.ATTRIBUTE_NAME = 'material', a.ATTRIBUTE_VALUE, null)) as material,
  MAX(IF(a.ATTRIBUTE_NAME = 'size', a.ATTRIBUTE_VALUE, null)) as size
from product p
left join product_attribute t
  on p.part_number = t.part_number
left join attribute a
  on t.attributeid = a.attributeid
group by p.part_number, p.name, p.price;

See SQL Fiddle with Demo. Data is returned in the format:

PART_NUMBER | NAME       | PRICE | COLOR | MATERIAL | SIZE
_________________________________________________________________
1           | product1   | 50    | red   | chrome   | null
2           | product2   | 96    | blue  | null     | x-large

As you case see the data might be in a better format for you, but if you have an unknown number of attributes, it will easily become untenable due to hard-coding attribute names, so in MySQL you can use prepared statements to create dynamic pivots. Your code would be as follows (See SQL Fiddle With Demo):

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(a.attribute_name = ''',
      attribute_name,
      ''', a.attribute_value, NULL)) AS ',
      attribute_name
    )
  ) INTO @sql
FROM attribute;

SET @sql = CONCAT('SELECT p.part_number
                    , p.name
                    , ', @sql, ' 
                   from product p
                   left join product_attribute t
                     on p.part_number = t.part_number
                   left join attribute a
                     on t.attributeid = a.attributeid
                   GROUP BY p.part_number
                    , p.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This generates the same result as the second version with no need to hard-code anything. While there are many ways to model this I think this database design is the most flexible.

Mysql – Select all children from infinite parent-child relations

Regarding the "SELECT", i.e. how to recursively get all the descendants of a parent, there a lot of answers in this question:

Find highest level of a hierarchical field: with vs without CTEs

Rolando's method uses variables and a procedure.
a_horse_with_no_name's method uses a recursive CTE. This is not possible in MySQL yet but you could switch to MariaDB 10.2 which has them implemented.

Hierarchical queries in MySQL

The blog post by @Quassnoi has detailed explanation about writing recursive queries in MySQL.

For the recursive DELETE, there is a simple method available in all MySQL versions:

Add a FOERIGN KEY constraint - if you haven't already - with the ON DELETE CASCADE option. Then a simple delete of a node will delete all its descendants.
You could modify the methods in the above links, to do a DELETE instead of SELECT.

Best Answer

Related Solutions

Product Attribute List Design Pattern in MySQL

Mysql – Select all children from infinite parent-child relations

Related Question