I am beginner to SQL, this is my first exercise to create a stored procedure in SQL. I need to get all the rows of all the persons with columns FirstName
, MiddleName
, LastName
, email address
, phone number
and phonenumber type
.
I have to join (required to use join) these 4 tables Person.person
, person.personphone
, person.phonenumbertype
, person.emailaddress
and retrieve the columns mentioned above.
The data I am using is the AdventureWorks 2016 SQL Server sample database, which has around 20k rows.
I tried inner joining on two tables to start with and the execution seems never ending.
select FirstName as firstname
from Person.Person
inner join person.EmailAddress on Person.Person.BusinessEntityID = Person.EmailAddress.BusinessEntityID
Thanks allmhuran and marc_s for the corrections.
Advice any link or suggestions on how I can get solution to this query.
CodePudding user response:
You need something like this:
-- select the columns you want
SELECT
p.FirstName, p.MiddleName, p.LastName,
pe.EmailAddress, pp.PhoneNumber, pnt.Name AS PhoneNumberType
FROM
-- this is your "base" table - where most of the info exists
Person.Person p
INNER JOIN
-- join to the e-mail table - based on "BusinessEntityID", to get e-mail address
Person.EmailAddress pe ON pe.BusinessEntityID = p.BusinessEntityID
INNER JOIN
-- join to the person phone table - based again on "BusinessEntityID", to get phone number
Person.PersonPhone pp ON pp.BusinessEntityID = p.BusinessEntityID
INNER JOIN
-- join the PersonPhone table to the PhoneNumberType table, to get the type of phone
Person.PhoneNumberType pnt ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID
You should always use proper / meaningful aliases for your tables - this makes your list of columns being selected, and your JOIN conditions, just so much more readable!