# 175. 组合两个表
# https://leetcode-cn.com/problems/combine-two-tables/

# SQL架构
Create table If Not Exists Person (personId int, firstName varchar(255), lastName varchar(255));
Create table If Not Exists Address (addressId int, personId int, city varchar(255), state varchar(255));
Truncate table Person;
insert into Person (personId, lastName, firstName) values ('1', 'Wang', 'Allen');
insert into Person (personId, lastName, firstName) values ('2', 'Alice', 'Bob');
Truncate table Address;
insert into Address (addressId, personId, city, state) values ('1', '2', 'New York City', 'New York');
insert into Address (addressId, personId, city, state) values ('2', '3', 'Leetcode', 'California');

# Write your MySQL query statement below
select
    p.firstname as firstname,
    p.lastname as lastname,
    a.city as city,
    a.state as state
from
    Person p
    left join Address a on a.personid = p.personid;

# clean-up
drop table Person;
drop table Address;