CODEWITHSUNDEEP
sqlsql-server
Kratocus
Kratocus

Reputation: 73

Using join incorrectly?

So I can't seem to get results with my joins. I'm wondering if I'm joining these tables wrong or I'm doing something else wrong. I've looked over the tables and it seems right.

USE AdventureWorks2016;

SELECT 
    C.CustomerID, P.FirstName, P.LastName, A.City, S.StateProvinceCode
FROM 
    Sales.Customer AS C 
JOIN 
    Person.BusinessEntityContact AS BC ON BC.PersonID = C.PersonID 
JOIN 
    Person.Person AS P ON P.BusinessEntityID = BC.BusinessEntityID
JOIN 
    Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = BC.BusinessEntityID
JOIN 
    Person.Address AS A ON A.AddressID = BA.AddressID
JOIN 
    Person.StateProvince AS S ON S.StateProvinceID = A.StateProvinceID

Upvotes: 0

Views: 90

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

You are trying to select customers that are their own business contact, and there are no such customers. I suggest to remove the BusinessEntityContact table from the query and try this instead:

SELECT C.CustomerID, P.FirstName, P.LastName, A.City, S.StateProvinceCode
FROM Sales.Customer AS C 
  JOIN Person.Person AS P ON C.PersonID = P.BusinessEntityID 
    JOIN Person.BusinessEntityAddress AS BA ON P.BusinessEntityID = BA.BusinessEntityID 
      JOIN Person.Address AS A ON BA.AddressID = A.AddressID 
        JOIN Person.StateProvince AS S ON A.StateProvinceID = S.StateProvinceID

Upvotes: 1

Amin Mozhgani
Amin Mozhgani

Reputation: 604

I think you have had a mistake in joining Person table to BusinessEntityContact. Based on the query you provided, I think you should join these tables based on PersonId field.

SELECT C.CustomerID, P.FirstName, P.LastName, A.City, S.StateProvinceCode
FROM Sales.Customer AS C JOIN Person.BusinessEntityContact AS BC ON 
BC.PersonID = C.PersonID 
JOIN Person.Person AS P ON P.PersonID = BC.PersonID 
JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = 
BC.BusinessEntityID
JOIN Person.Address AS A ON A.AddressID = BA.AddressID
JOIN Person.StateProvince AS S ON S.StateProvinceID = A.StateProvinceID

Upvotes: 1

Related Questions