Labels

Tuesday, March 3, 2015

Combine Two Tables

Table: Person
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.
Table: Address
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:
FirstName, LastName, City, State 
 
Naive Way : "regardless if there is an address for" means address could be null. So use left outer join to combine two tables. Left join ensures that the left table cannot be null, right table may use null value to match left table.

 SELECT FirstName, LastName, City, State  
 FROM Person LEFT OUTER JOIN Address  
 ON Person.PersonId = Address.PersonId;  

No comments:

Post a Comment