Create stored procedure and write a query for joining multi table based on columns in SQL Server

user_11737583 0
Viewed 44

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.

join stored-procedures tsql sql-server
1 Answers

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!

user_133020
Related