How to view all sales order details by using SQL statment.?

Question

i have tables customer(CustomerID,CustomerName),,, Item_master(Item_code,Item_Name) sales_Order(orderno,customerId) and orderdetails(Orderno,item_code,qty) i want sql query or view in which all orderdetails order to display like orderno|CustomerName|Item_Name|qty please guide...

Priyanka Jain · Answer

As per your query Select Query with Inner Joing is as below SELECT o.Orderno,c.CustomerName,i.Item_code,o.qty FROM orderdetails o INNER JOIN Item_master i ON o.item_code = i.Item_code INNER JOIN sales_Order s on s.orderno = o.Orderno INNER JOIN customer c on c.CustomerID = s.customerId And to create view below code is used CREATE VIEW OrderDetail AS ( SELECT o.Orderno,c.CustomerName,i.Item_code,o.qty FROM orderdetails o INNER JOIN Item_master i ON o.item_code = i.Item_code INNER JOIN sales_Order s ON s.orderno = o.Orderno INNER JOIN customer c ON c.CustomerID = s.customerId ) SELECT * FROM OrderDetail

Gajendra Jangid · Answer

Hello Akhter use below query select od.orderno,cm.CustomerName,im.Item_Name,od.qty from sales_Order as so inner join orderdetails od on so.orderno=od.Orderno inner join Item_master im on od.Item_code=im.Item_code inner join customer cm on so.customerId=cm.CustomerID

satheesh d · Answer

To retreive the data from mutiple tables, Joins in SQL can be used to join the multiple tables. Joining the table is possible only atleast one column should match with another table for joining. To answer for the abouve query SELECT order .Orderno, cust.CustomerName , master.Item_Name, detail.qty FROM customer cust JOIN Sales_Order order ON order .customerId = cust.customerId JOIN orderdetails detail ON detail.orderno = order .orderno JOIN Item_master master ON master.Item_code = detail.Item_code