1

I am using MySQL database and in that two tables, relation is one to many. One table is product table, sample data:

+----+----------+
| id |   name   |
+----+----------+
|  1 | product1 |
|  2 | product2 |
+----+----------+

Other table is attribute table

+------------+----------+------------+
| product_id |   key    |   value    |
+------------+----------+------------+
|          1 | format   | mp3        |
|          1 | format   | audio      |
|          1 | category | English    |
|          1 | category | Vocabulary |
|          2 | format   | mp3        |
|          2 | format   | book       |
|          2 | category | Grammar    |
|          2 | category | German     |
+------------+----------+------------+

What i want to achieve is that my query should have different columns for different key used in attribute table.

Result:

+------------+----------+-----------+--------------------+
| product_id |   name   |  format   |      category      |
+------------+----------+-----------+--------------------+
|          1 | product1 | mp3,audio | English,Vocabulary |
|          2 | product2 | mp3,book  | German,Grammar     |
+------------+----------+-----------+--------------------+

So far i came up with the solution including joining attribute table twice, like as follows:

SELECT a.*, 
GROUP_CONCAT(b.value,',') as format, 
GROUP_CONCAT(c.value,',') as category 
FROM products as a 
LEFT JOIN attributes ON a.id=b.product_id AND key = 'format' as b 
LEFT JOIN attributes as c ON a.id=c.product_id AND key = 'category' 
GROUP BY a.id

Is there anything wrong with this solution (joining same table twice)? Or should it be done some other way?

--data population:

DROP TABLE IF EXISTS `attributes`;
CREATE TABLE `attributes` (
  `product_id` int(11) NOT NULL DEFAULT '0',
  `product_key` varchar(50) DEFAULT NULL,
  `product_value` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of attributes
-- ----------------------------
INSERT INTO `attributes` VALUES ('1', 'format', 'mp3');
INSERT INTO `attributes` VALUES ('1', 'format', 'audio');
INSERT INTO `attributes` VALUES ('1', 'category', 'English');
INSERT INTO `attributes` VALUES ('1', 'category', 'Vocabulary');
INSERT INTO `attributes` VALUES ('2', 'category', 'Grammar');
INSERT INTO `attributes` VALUES ('2', 'category', 'German');
INSERT INTO `attributes` VALUES ('2', 'format', 'mp3');
INSERT INTO `attributes` VALUES ('2', 'format', 'book');

-- ----------------------------
-- Table structure for `products`
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES ('1', 'product1');
INSERT INTO `products` VALUES ('2', 'product2');
4
  • Do it with a case you only need a join sqlfiddle.com/#!2/aa40d/1 Also read about database normalization,attribute table should be split.
    – Mihai
    Jul 29 '14 at 8:55
  • I don't want to split tables, because these attributes groups can be created by operator. So finally keys, will reside in different table. Actually CASE solution is pretty neat too. Simplifies other things for me.
    – Piotr
    Jul 29 '14 at 9:08
  • 1
    Please next time you could dump the table as below to help fellow programmers solve your problems easily and save a whole lot of time. The question has been edited to demonstrate some helpful ways to do that.
    – Toniton
    Jul 29 '14 at 11:39
  • For sure I will. Someone although did uncool edit, changing query that I asked about.
    – Piotr
    Jul 29 '14 at 15:44
1

AFAIK the only other way is to run 2 queries and add the columns in the application.

Your solution can work with only one JOIN if you put some IF's in your GROUP_CONCAT's:

`GROUP_CONCAT(IF(a.key = 'format',a.value,NULL))` as formats,
`GROUP_CONCAT(IF(a.key = 'category',a.value,NULL))` as categories,
...

It is debatable if the single JOIN option is better...

1
  • It's also viable solution for me.
    – Piotr
    Jul 29 '14 at 9:10

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.