如何使用SQL语句查看所有销售订单详细信息。 [英] How to view all sales order details by using SQL statment.?

问题描述

我有表客户（CustomerID，客户名称）,,, Item_master（Item_code，Item_Name）

sales_Order（orderno，customerId）和orderdetails（Orderno，item_code，qty）





i想要sql查询或查看所有orderdetails命令显示。

喜欢



orderno |客户名称| Item_Name |数量



请指导...



我尝试过：

选择SalesOrder.OrderNo，ItemMasterFile.Descriptionitem，
 OrderDetail.orderqty ，来自OrderDetail的Customer.CustomerName 
内部联接ItemMasterFile上的ItemMasterFile.CodeItem = OrderDetail.CodeItem 
内部联接Customer.CustomerID上的客户= SalesOrder.CustomerID 

解决方案

您错过了SalesOrder表的连接。例如。

 选择 S.OrderNo，I.Item_Name，OD.qty，C.CustomerName 
 来自 sales_Order S 
 内部  join  orderdetails OD  ON  OD.Orderno = S.orderno 
  inner   join  Item_master我 on  I.Item_code = OD.Item_code 
  inner   join 客户C  on  C.CustomerID = S.customerId 

请注意我的解决方案中的表名和列名是您在说明中提到的那些，而不是您在代码中使用的那些。我不倾向于回去更改它们。

如果对查询应用一些格式化，并且使用诸如Sql Studio之类的工具，则可以更容易地进行故障排除因任何错误而导致 red lined 查询。特别是如果您尝试运行它

 选择 SalesOrder.OrderNo 
，ItemMasterFile.Descriptionitem 
，OrderDetail.orderqty 
，Customer.CustomerName 
 来自 OrderDetail 
  inner   join  ItemMasterFile  on  ItemMasterFile.CodeItem = OrderDetail.CodeItem 
  inner   join 客户  Customer.CustomerID = SalesOrder.CustomerID 

第一件事但是，你的SELECT列表中有SalesOrder.OrderNo; SalesOrder既不是FROM源也不是JOINed。



我建议的另一个提示是使用别名作为表名，它也有帮助可读性

  SELECT  s.OrderNo 
，i.Descriptionitem 
，d.Qty 
，c .CustomerName 
  FROM  Sales_Order s 
  INNER   JOIN  OrderDetail d  ON  s.OrderNo = d.OrderNo 
  INNER   JOIN  Item_master i  ON  d.Item_code = i.Item_code 
  INNER   JOIN 客户c  ON  c.CustomerID = s.CustomerID 

i have table 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...

What I have tried:

select SalesOrder.OrderNo,ItemMasterFile.Descriptionitem,
OrderDetail.orderqty,Customer.CustomerName from OrderDetail 
inner join ItemMasterFile on ItemMasterFile.CodeItem=OrderDetail.CodeItem
inner join Customer on Customer.CustomerID=SalesOrder.CustomerID

解决方案

You have missed out the join to the SalesOrder table. E.g.

select S.OrderNo,I.Item_Name,OD.qty,C.CustomerName 
from sales_Order S
inner join orderdetails OD ON OD.Orderno = S.orderno
inner join Item_master I on I.Item_code=OD.Item_code
inner join Customer C on C.CustomerID = S.customerId

Note the table and column names in my solution are those that you mentioned in your description and not those that you have used in your code. I was not inclined to go back and change them.

It makes it a lot easier to troubleshoot if you apply a little formatting to your query, and using a tool such as as Sql Studio should have red lined the query due to any errors. Especially if you tried running it

select SalesOrder.OrderNo
,      ItemMasterFile.Descriptionitem
,      OrderDetail.orderqty
,      Customer.CustomerName
from OrderDetail 
inner join ItemMasterFile on ItemMasterFile.CodeItem   =OrderDetail.CodeItem
inner join Customer       on Customer.CustomerID       =SalesOrder.CustomerID

The first thing you have in your SELECT list is SalesOrder.OrderNo, however; SalesOrder is neither the FROM source nor is it JOINed.

Another tip I recommend is to use aliases for the table names, it also helps in readability

SELECT  s.OrderNo
  ,    	i.Descriptionitem
  ,     d.Qty
  ,     c.CustomerName
FROM        Sales_Order  s				
INNER JOIN  OrderDetail  d ON s.OrderNo    = d.OrderNo
INNER JOIN  Item_master  i ON d.Item_code  = i.Item_code
INNER JOIN  customer     c ON c.CustomerID = s.CustomerID

这篇关于如何使用SQL语句查看所有销售订单详细信息。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！