Fetching list of foreign key in a single statement MySql

Question

I am trying to get all units related to a product in a single query

Would appreciate any help

Table Product 

ID  Product_Name
1    Tomato 
2    Banana
3    Sugar
4    Potatto 

Table Units 

ID ProductID Quantity Unit
1     3         1      KG
2     3         2      KG
3     3         3      KG
4     3         5      KG

Expected Result 

 ID 1, Product_Name Sugar, [Quantity 1 Unit KG] [Quantity 2 Unit KG] [Quantity 3 Unit KG] [Quantity 5 Unit KG]

Answer 1

After joining Products and Units, you can group the result by product columns, then using the GROUP_CONCAT aggregate function get the desired unit list for the product:

SELECT p.ID, p.Product_Name, GROUP_CONCAT(CONCAT('[Quantity:', u.Quantity, '  Unit:',u.Unit,']')) as UnitList
FROM Units u JOIN Product p
ON p.ID = u.ProductId
WHERE p.ID = 3 // or any other product id
GROUP BY p.ID, p.Product_Name