joining 4 tables in adventureworks2016

Question

Post reply

joining 4 tables in adventureworks2016

sql_DB_exprt

SSC Enthusiast

Points: 180
More actions
September 7, 2019 at 8:30 am

#3678167

Hi can anyone please help me to know that what is the issue with this query

select Person.Person.LastName, sales.Customer.CustomerID, SD.SalesOrderID, Sales.SalesOrderHeader.OrderDate, SD.ProductID,SD.OrderQty, SD.OrderQty , SD.UnitPrice* SD.OrderQty as total from person.Person inner join
Sales.Customer
on Customer.PersonID=Person.BusinessEntityID
join Sales.Customer as SC on
Customer.CustomerID=SC.CustomerID
join
Sales.SalesOrderDetail SD on
sd.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
where SD.SalesOrderID =43659
i have error

Msg 4104, Level 16, State 1, Line 91
The multi-part identifier "Sales.SalesOrderHeader.SalesOrderID" could not be bound.
Msg 4104, Level 16, State 1, Line 84
The multi-part identifier "Sales.SalesOrderHeader.OrderDate" could not be bound
i am expecting result as
the person name with all order details

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16014 More actions · Answer 1

Few errors

Referencing a table on the select columns that is not part of the joins - that is your main error

Giving alias to some tables, and then using the full 3 part naming instead of the alias.

always use alias in all tables and use that alias to identify the columns - I replace the missing/incorrect ones on your query, along side with some formatting to make it more readable.

but the table Sales.SalesOrderHeader which is the main error you are getting needs to be added by yourself to the query

select per.LastName
     , sc1.CustomerID
     , sd.SalesOrderID
     , Sales.SalesOrderHeader.OrderDate -- this table is not on the joins so can't be referenced on this query
     , sd.ProductID
     , sd.OrderQty
     , sd.OrderQty
     , sd.UnitPrice * sd.OrderQty as total
from person.Person pp
inner join Sales.Customer sc
    on Customer.PersonID = pp.BusinessEntityID
inner join Sales.Customer as sc1
    on Customer.CustomerID = sc.CustomerID
inner join Sales.SalesOrderDetail sd
    on sd.SalesOrderID = sd.SalesOrderID
where sd.SalesOrderID = 43659

sql_DB_exprt SSC Enthusiast Points: 180 More actions · Answer 2

Thanks for the reply champion

this is the main problem that i am not able to join Sales.SalesOrderHeader

I am joining this at the end as

sales.SalesOrderDetail.SalesOrderID = sales.salesorderheader.salesorderID

but getting error message I have reported earlier

"The multi-part identifier "Sales.SalesOrderHeader.SalesOrderID" could not be bound.

frederico_fonseca SSCoach Points: 16014 More actions · Answer 3

as I said you need to add a join to the sales.salesorderheader table - that is what you are missing. putting "sales.SalesOrderDetail.SalesOrderID = sales.salesorderheader.salesorderID" is not joining - it is stating that should you have 2 tables on the join (salesorderdetail and salesorderheader) they should be joined by column SalesOrderID. But as you did not add the second table (header) the sql is invalid

sql_DB_exprt SSC Enthusiast Points: 180 More actions · Answer 4

ah i found issue in my query and have resolved it by writing logic on a paper 1st

the right query is as follows and its working for me

select pp.firstname +'_'+

pp.LastName as CustomerName

,convert (date,sh.OrderDate) as orderdate

,sd.SalesOrderID

,sd.OrderQty

,sd.UnitPrice

,sd.UnitPrice* sd.OrderQty as total

from Person.Person as pp

inner join

sales.Customer as SC

on

pp.BusinessEntityID= sc.CustomerID

join

Sales.SalesOrderHeader as Sh

on

sc.CustomerID=sh.CustomerID

join

Sales.SalesOrderDetail as SD

on

sd.SalesOrderID= sh.SalesOrderID

sql_DB_exprt SSC Enthusiast Points: 180 More actions · Answer 5

yes it was typo and i did not check earlier thanks for your reply