inner join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

inner join

Author

Topic

madhan
Yak Posting Veteran

59 Posts

Posted - 2014-12-28 : 13:36:57

Hi - I am using Adventureworks2012 database to understand concepts of inner join.

for ex,consider the following query problem

Write a query that displays the names of the customers along with the product names that they have purchased. Hint:five tables will be reuired to write this query.

I have hard time figuring out what are those five tables. All I have the idea of using tables:Production.Product, Sales.Customer tables at this point.

Please help me how to work on finding which tables that I need to include to obtain results.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-28 : 14:09:17

Sales.Customer has a CustomerId column. So does the Sales.SalesOrderHeader table. That means you can write

select *
from Sales.Customer c
inner join Sales.SalesOrderHeader o
on c.customerID = o.customerId

Now, look at the SalesOrderHeader and SalesOrderDetail tables. They have a column in common: SalesOrderId. Using that you can also join those two. Order Details contains ProductId, So does the table Production.Product. and so forth...

madhan
Yak Posting Veteran

59 Posts

Posted - 2014-12-28 : 15:38:46

Thanks for the help. I have joined tables like below now

select pro.Name as NameOfProduct,proSub.ProductSubcategoryID
from Sales.Customer as cust
inner join Sales.SalesOrderHeader as soh
on cust.CustomerID = soh.CustomerID
inner join Sales.SalesOrderDetail as sod
on soh.SalesOrderID = sod.SalesOrderID
inner join Production.Product as pro
on pro.ProductID=sod.ProductID
inner join Production.ProductSubcategory as proSub
on pro.ProductSubcategoryID = proSub.ProductSubcategoryID

I am not sure in the join I need to use 5 th table as Production.ProductSubcategory or Production.ProductModel table. I guess we can join tables depends on the select list of columns that we need to display. Correct?

I am trying to display the name of the customer in select list. but I don;t find the name of the customer in Customer table. Any idea, please

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-28 : 16:01:35

I think in this case a customer is a person. There is a personid in the customer table that you can use

madhan
Yak Posting Veteran

59 Posts

Posted - 2014-12-28 : 16:20:30

Thank you very much again for the help. now the query problem solved and well understood the concept.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources