mysqljoinviewsubqueryconcat-ws

Turn MySQL key pair into concatenated list


I have 3 tables:

businesses ( id, name )
categories ( id, name )
business_categories ( business_id, category_id )

The 3rd table is many to many, and there is a PRIMARY KEY on that pair.

I am trying to create a view that would be something like:

business_name    category_names
Pizza Hut        Restaurants, Pizza, Fast Food
Chipotle         Restaurants, Mexican, Fast Food

Here is what I am trying

SELECT `businesses`.`name`, CONCAT_WS( ',', (
    SELECT `name`
    FROM `categories`
    JOIN `business_categories`
        ON `categories`.`id` = `business_categories`.`category_id`
    WHERE `business_categories`.`business_id` = `businesses`.`id`
) ) 
FROM  `businesses` 
ORDER BY  `businesses`.`id` ASC 

But I am getting this error:

Subquery returns more than 1 row

Solution

  • You can achieve that with GROUP_CONCAT() function:

    SELECT
      businesses.name,
      GROUP_CONCAT(categories.name) AS all_categories
    FROM
      businesses
        LEFT JOIN business_categories
          ON businesses.id=business_categories.business_id
        LEFT JOIN categories
          ON business_categories.category_id=categories.id
    GROUP BY
      businessess.name