Select subquery error Subquery returns more than 1 row

Table 1

+------------+--------------------+                             
| Serialnum  | Products           |                             
+------------+--------------------+                             
|     1      | Earbuds            |                             
|     2      | Phone              |                             
|     3      | Charger            |                             
|     4      | Data Cable         |                             
|     5      | Speakers           |                             
+------------+--------------------+

Table 2

+------------+--------------------+
| product_id | Brands             |
+------------+--------------------+
|     1      | Samsung            |
|     1      | Xaomi              |
|     1      | JBL                |
|     2      | Ronin              |
|     2      | Apple              |
+------------+--------------------+                               

SELECT products.Serialnum,
        products.Product_Name,
        (SELECT GROUP_CONCAT(brand_name) 
         FROM products_brands 
         GROUP BY product_id) 
FROM `products`
    INNER JOIN products_brands ON products.Serialnum=products_brands.product_id

#1242 – Subquery returns more than 1 row

I am trying to get concatenated values from the other table with matching ids from this table that I have given in the foreign key like this:

+------------+------------------------------+
| Products   | Brands                       |
+------------+------------------------------+
|Earbuds     |Samsung,Xaomi,JBL,Ronin,Apple |
|Phone       | Xaomi,Samsung,Apple          |
|Data Cable  | Ronin,Apple,Samsung          |
|Speakers    | JBL                          |
|Charger     | Ronin,Apple,Samsung          |
+------------+------------------------------+                               

Edit: I changed the code:

SELECT products.Serialnum,products.Product_Name FROM `products`
INNER JOIN (SELECT GROUP_CONCAT(brand_name) 
FROM products_brands 
GROUP BY product_id) products_brands
ON products.Serialnum=products_brands.product_id

New Error:#1054 – Unknown column ‘products_brands.product_id’ in ‘on clause’

Answer

Your expected result based on your data I think is wrong.

CREATE TABLE products(
             Serialnum int(9),
             Products VARCHAR(50)
                                 );

insert into products values (1,'Earbuds'),
                            (2,'Phone'),
                            (3,'Charger'),
                            (4,'Data Cable'),
                            (5,'Speakers');


CREATE TABLE products_brands(
             product_id int(9),
             Brands VARCHAR(50)
                              );                            
                                      
 insert into products_brands values (1,'Samsung'),
                                    (1,'Xaomi'),
                                    (1,'JBL'),
                                    (1,'Ronin'),
                                    (1,'Apple'); 

And the correct query should be:

SELECT products.Products,
        GROUP_CONCAT(Brands) as Brands
FROM  products 
INNER JOIN products_brands ON products.Serialnum=products_brands.product_id
GROUP BY Products;
     

Demo: https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/6