175. Combine Two Tables

Description

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

Constraints

Approach

Links

• GeeksforGeeks

• Leetcode

• ProgramCreek

• YouTube

Examples

Example 1

Input:

{ "headers": { "Person": [ "PersonId", "LastName", "FirstName" ], "Address": [ "AddressId", "PersonId", "City", "State" ] }, "rows": { "Person": [ [ 1, "Wang", "Allen" ] ], "Address": [ [ 1, 2, "New York City", "New York" ] ] } }

Output:

{"headers": ["FirstName", "LastName", "City", "State"], "values": [["Allen", "Wang", null, null]]}

Solutions

Solution 1

# MySQL query statement

SELECT FirstName, LastName, City, State 
FROM Person p
LEFT JOIN Address a 
ON p.PersonId = a.PersonId

Follow up