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'
CodePudding user response:
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;