Different column for different conditions when joining 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');
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...