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’

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating