How to select and count data from three tables in mysql

Question

I have three tables

1 - Products 
2- Orders
3- Order_item

Table (Products)

ID | Product_name | 
--------------------
1  | Samsung Galaxy

Table (Orders)

ID | Client_name 
-------------------
1  | Admin

Table (Orders_item)

ID | Order_ID | Product_ID | Quantity 
-------------------------------------
1  | 1        | 1          | 1

2  | 1        | 1          | 1 

3  | 1        | 1          | 1

I want to select product name , client name and number of quantities when order is 1. I have tried this query

SELECT products.name,orders.order_id,orders.client_name,order_item.quantity COUNT(*) FROM products,orders,order_item WHERE products.id = order_item.product_id AND orders.order_id = order_item.order_id AND orders.order_id=1

Which returns result like

Name | Order_ID | Client_name | Quantity 
----------------------------------------
Sa..| 1         | Admin       | 1 
Sa..| 1         | Admin       | 1 
Sa..| 1         | Admin       | 1

I want to get the the result like

Samsung | 1 | Admin | 3

Someone please help me .

Note that we stopped writing queries this way ca. 1992. See about JOINs. And then see about GROUP BY. — Strawberry, Oct 03 '19 at 07:03
To give context to Strawberry's comment: Please don't use Old comma based Implicit joins and use Modern [Explicit JOIN based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Oct 03 '19 at 07:04
Also, Hint: You need to `GROUP BY` on order and product. Worth looking a tutorial on GROUP BY in SQL — Madhur Bhaiya, Oct 03 '19 at 07:04

Cid · Answer 1 · 2019-10-03T07:13:05.760

You need a GROUP BY

SELECT products.Product_name AS Name,
       orders.id AS Order_ID,
       orders.client_name AS Client_name,
       SUM(order_item.Quantity) AS Quantity
FROM products
LEFT JOIN order_item 
ON order_item.Product_ID = products.ID
LEFT JOIN orders
ON orders.ID = order_item.Order_ID
WHERE orders.id=1
GROUP BY products.Product_name,
         orders.id,
         orders.client_name

This outputs :

| Name           | Order_ID | Client_name | Quantity |
| -------------- | -------- | ----------- | -------- |
| Samsung Galaxy | 1        | Admin       | 3        |

Note that JOIN can be a good reading too.

How to select and count data from three tables in mysql

1 Answers1