CODEWITHSUNDEEP
sqlsql-servert-sqljoinrelational
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10267

How can I relate two tables in SQL that are related through a third one?

My SQL skills have atrophied and I need some help connecting two tables through a third one that contains foreign keys to those two.

The Customer table has data I need. The Address table has data I need. They are not directly related to each other, but the CustomerAddress table has both CustomerID and AddressID columns.

Specifically, I need from the Customer table:

FirstName
MiddleName
LastName

...and from the Address table:

AddressLine1
AddressLine2
City
StateProvince, 
CountryRegion
PostalCode

Here is my awkward attempt, which syntax LINQPad does not even recognize ("Incorrect syntax near '='").

select C.FirstName, C.MiddleName, C.LastName, A.AddressLine1, A.AddressLine2, A.City, A.StateProvince, 
A.CountryRegion, A.PostalCode
from SalesLT.Customer C, SalesLT.Address A, SalesLT.CustomerAddress U
left join U.CustomerID = C.CustomerID
where A.AddressID = U.AddressID

Note: This is a SQL Server table, specifically AdventureWorksLT2012_Data.mdf

Upvotes: 1

Views: 210

Answers (3)

JBrooks
JBrooks

Reputation: 10013

The LEFT JOIN will include the customers that don't have an address.

    SELECT C.FirstName, C.MiddleName, C.LastName, A.AddressLine1, 
    A.AddressLine2, A.City, A.StateProvince, 
    A.CountryRegion, A.PostalCode
    FROM  Customer C
    LEFT JOIN CustomerAddress U 
    ON U.CustomerID = C.CustomerID  
    LEFT JOIN Address A
    ON A.AddressID = U.AddressID
    ORDER BY C.LastName, C.FirstName

Upvotes: 1

jdl
jdl

Reputation: 1104

SELECT
    c.FirstName,
    c.MiddleName,
    c.LastName,
    a.AddressLine1
    a.AddressLine2
    a.City
    a.StateProvince, 
    a.CountryRegion
    a.PostalCode
FROM Address a
JOIN CustomerAddress ca
    ON ca.AddressID = a.AddressID
JOIN Customer c
    ON c.CustomerID = ca.CustomerID
WHERE ...

Upvotes: 3

M.Ali
M.Ali

Reputation: 69524

select C.FirstName, C.MiddleName, C.LastName, A.AddressLine1, A.AddressLine2, A.City, A.StateProvince, 
A.CountryRegion, A.PostalCode
from  SalesLT.CustomerAddress U INNER JOIN SalesLT.Address A
ON A.AddressID = U.AddressID
INNER JOIN  SalesLT.Customer C
ON U.CustomerID = C.CustomerID

I have only used INNER JOINS but obviously you can replace them with LEFT or RIGHT joins depending on your requirements.

Upvotes: 3

Related Questions