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’
Advertisement
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;