Adventureworks joining sales.store with sales.salesorderdetail to pull in store name

Question

I'm working on some adventureworks practice queries and am having some issues.

I'm trying to pull in the store name from the sales.store table and add it to the sales.salesorderdetail table so I can see which "stores" are ordering specific items.

MY problem is that I can't seem to find a common key that will allow me to do that unless customerid = businessentityid, which I don't believe it does.

I thought I could use the person.person table to pull this in but since every businessentityid does not necessarily have a "person", I don't think this will work.

Is anyone familiar enough with adventureworks to help me out?

Answer 1

select * 
from sales.Store
inner join sales.Customer on sales.Store.BusinessEntityID = sales.Customer.StoreID
inner join sales.SalesOrderHeader on sales.Customer.CustomerID = sales.SalesOrderHeader.CustomerID
inner join sales.SalesOrderDetail on sales.SalesOrderHeader.SalesOrderID = sales.SalesOrderDetail.SalesOrderID

To find the relationship, right click the Store table in SSMS and View Dependencies. The resulting tree shows that store connects to SalesOrderDetail via Customer and SalesOrderHeader.

Also, here's the constraint showing that Store.BusinessEntityID joins to Customer.StoreID:

ALTER TABLE [Sales].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Store_StoreID] FOREIGN KEY([StoreID])
REFERENCES [Sales].[Store] ([BusinessEntityID])
GO