table structure is as follows
-- Table structure for table category
CREATE TABLE `category` (
`cat_id` int(10) NOT NULL auto_increment,
`heading` varchar(255) NOT NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `category` (`cat_id`, `heading`) VALUES
(1, 'Fashion'),
(2, 'Kids');
-- --------------------------------------------------------
-- Table structure for table `shop`
CREATE TABLE `shop` (
`store_id` int(10) NOT NULL auto_increment,
`shop_name` varchar(255) NOT NULL,
`cat_id` int(10) NOT NULL,
`subcat_id` int(10) NOT NULL,
PRIMARY KEY (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `shop` (`store_id`, `shop_name`, `cat_id`, `subcat_id`) VALUES
(1, 'Test Store', 1, 1),
(2, 'Test Store 1', 1, 1),
(3, 'Another Store', 1, 3);
-- --------------------------------------------------------
-- Table structure for table `subcategory`
CREATE TABLE `subcategory` (
`subcat_id` int(10) NOT NULL auto_increment,
`cat_id` int(10) NOT NULL,
`heading` varchar(255) NOT NULL,
PRIMARY KEY (`subcat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `subcategory` (`subcat_id`, `cat_id`, `heading`) VALUES
(1, 1, 'Women'),
(2, 1, 'General'),
(3, 1, 'Men'),
(4, 2, 'Children');
if i use the below query i get the following output
SELECT
`category`.`heading` AS `category`
, `subcategory`.`heading` AS `subcategory`
, COUNT(`shop`.`subcat_id`) AS cnt
FROM
`test`.`shop`
INNER JOIN `test`.`subcategory`
ON (`shop`.`subcat_id` = `subcategory`.`subcat_id`)
INNER JOIN `test`.`category`
ON (`shop`.`cat_id` = `category`.`cat_id`)
GROUP BY `shop`.`subcat_id`
HAVING (COUNT(`shop`.`subcat_id`) !='');
<kbd>category</kbd><kbd>subcategory</kbd><kbd>cnt</kbd>
<kbd>Fashion</kbd><kbd>Women</kbd><kbd>2</kbd>
<kbd>Fashion</kbd><kbd>Men</kbd><kbd>1</kbd>
but i want to group concat the subcategory like below
<kbd>category</kbd><kbd>subcategory</kbd>
<kbd>Fashion</kbd><kbd>Women,2|Men,1</kbd>