Need to write a complex query involving multiple Joins with related foreign key dependencies

Question

Main question

I need a SQL statement to answer this entire question, not using any Union statements. In the AdventureWorks2014 database I need to find which store is David Campbell associated with? And sort by store name.

• This query must show store name, as well as first/last as the first items in your select. This query needs to be done using joins between the various tables and their fk dependencies.

• Must not include joined tables with no foreign Key relationship.

Things I tried

I ran this query to get the schema id and object id's that are related to the store:

Select 
    object_name(t.object_id), Schema_name(t.schema_id), t.name, c.name
From 
    sys.tables t
Join 
    sys.columns c on t.object_id = c.object_id
Where 
    t.name like '%Store%'
    or c.name like'%Store%'

This let me identify specific tables of interest that I could join to. Table Sales.Customer with a FK name StoreID & PersonID, as well as table Sales.Store with column StoreName.

From there I looked at the dependencies of the fk's and found the first / last names in table Person.Person. I tried this query with no luck:

Select 
    s.name StoreName, p.FirstName, p.LastName
From 
    sales.customer c
Join 
    person.person p On c.personID = p.businessentityID
Join 
    sales.store s on s.businessentityID = p.businessEntityID
Where 
    p.FirstName Like ‘%David%’ 
    and p.LastName Like ‘%Campbell%’ 
Order by 
    s.Name

Problem

Unfortunately I cannot seem to isolate which stores are Associated with David Campbell. I know the joins are not right, but am unsure of how to link the "On" clauses and tables correctly to produce the right results based on their fk dependencies.

I know that two records from person.person are related by running this:

Select * 
From Person.Person
Where p.FirstName Like '%David%' 
  and p.LastName Like '%Campbell%'

This returns BusinessentityID's 283 and 609 which are associated with "David Campbell" in the table Person.Person, though no idea how to link those id's to the store names that are associated with david.

Basically I don't know specifically which joins are causing my query to give no results back. I can include more information about what I've tried if needed.

Looking for tips and feedback.

Thanks!

EDIT:

It appears my original approach is off. I went a different route starting with the foreign keys in Sales.Store and going from there. I also added a windows function to count the stores associated with "David Campbell" and added a null parameter to the where clause to account for the store that has no name.

Here is my final result:

select
     s.Name StoreName, p.FirstName, p.LastName,
     count(s.BusinessEntityID) Over(Partition by s.salespersonId) as TotalStoresAssociatedWithDavidCampbell         --<38 records show      a "David Campbell"
from 
    Sales.Store s
join 
    Sales.SalesPerson sp on s.SalesPersonID = sp.BusinessEntityID
Join 
    Person.BusinessEntity be on sp.BusinessEntityID = be.BusinessEntityID
Join 
    Person.Person p on p.BusinessEntityID = be.BusinessEntityID
where 
    p.FirstName like '%David%' 
    and p.LastName Like '%Campbell%' 
    or s.Name is null
order by s.Name  

Though, I do not know if this is the correct answer for the original question. I think it is, but I would feel better about it if I was certain.

Answer 1

I feel something doesn't match with the purpose in this part:

Join sales.store s On s.businessentityID = p.businessEntityID

In this part, the store has it relation with the customer. It is not direct relation with person.

store detail -> customer of the store -> person detail

Therefore, I guess you should connect store to customer rather than store to person.

I haven't tried this query because I'm not connecting to DB. This just an idea.

select S.Name StoreName, P.FirstName, P.LastName
from sales.customer C
join person.person P on P.BusinessEntityID = C.PersonID
join sales.store S on S.StoreID= C.StoreID
where P.FirstName = 'David' and P.LastName = 'Campbell'
order by S.Name

Answer 2

I have analysed the schema diagram of adventure database from this link

https://i.stack.imgur.com/LMu4W.gif and checked your queryjoin is right. But the problem is in Sales.Store table no BusinessEntityID is matched with the Person.Person table's BusinessEntityID thats why your query returned nothing in result