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

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

Posted 12-Apr-19 0:48am

akhter86

Updated 12-Apr-19 1:44am

Add a Solution

Comments

CHill60 12-Apr-19 7:07am

What is wrong with the code you have tried?

2 solutions

Solution 2

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

SQL

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

SQL

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

Posted 12-Apr-19 1:44am

MadMyche

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2019-04-12T01:27:00

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

SQL

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.