Data from 3 different tables - 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

Data from 3 different tables

Author

Topic

clausenmausen
Starting Member

2 Posts

Posted - 2013-11-17 : 00:42:45

Hi!
This is probably a very common SQL task but I'm hoping someone can help me out. I'm playing around with the AdventureWorks database in SQL Server Management Studio. Let's say I want to get a table that has FirstName, LastName, City and State for all employees that live in the state of Oregon. These are the tables.

Person
BusinessEntityID
FirstName
LastName

Address
AddressID
City
StateProvinceID

StateProvince
StateProvinceID
Name (of State)

BusinessEntityAddress
BusinessEntityID
AddressID

Another way I could explain what I need is with this obvious wrong code:

SELECT
Person.FirstName,
Person.LastName,
Address.City,
StateProvince.Name
WHERE
StateProvince.Name = 'Oregon'
;

What's the correct way to achieve this in one statement?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-17 : 01:06:09

[code]SELECT
Person.FirstName,
Person.LastName,
Address.City,
StateProvince.Name
FROM Person
INNER JOIN BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
INNER JOIN Address ON BusinessEntityAddress.AddressID = Address.AddressID
INNER JOIN StateProvince ON Address.StateProvinceID = StateProvince.StateProvinceID
WHERE
StateProvince.Name = 'Oregon'[/code]

KH
[spoiler]Time is always against us[/spoiler]

clausenmausen
Starting Member

2 Posts

Posted - 2013-11-17 : 01:30:36

Perfect! Thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources