1

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.