Welcome to Subscribe On Youtube
175. Combine Two Tables
Description
Table: Person
+-------------+---------+ | Column Name | Type | +-------------+---------+ | personId | int | | lastName | varchar | | firstName | varchar | +-------------+---------+ personId is the primary key (column with unique values) for this table. This table contains information about the ID of some persons and their first and last names.
Table: Address
+-------------+---------+ | Column Name | Type | +-------------+---------+ | addressId | int | | personId | int | | city | varchar | | state | varchar | +-------------+---------+ addressId is the primary key (column with unique values) for this table. Each row of this table contains information about the city and state of one person with ID = PersonId.
Write a solution to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Person table: +----------+----------+-----------+ | personId | lastName | firstName | +----------+----------+-----------+ | 1 | Wang | Allen | | 2 | Alice | Bob | +----------+----------+-----------+ Address table: +-----------+----------+---------------+------------+ | addressId | personId | city | state | +-----------+----------+---------------+------------+ | 1 | 2 | New York City | New York | | 2 | 3 | Leetcode | California | +-----------+----------+---------------+------------+ Output: +-----------+----------+---------------+----------+ | firstName | lastName | city | state | +-----------+----------+---------------+----------+ | Allen | Wang | Null | Null | | Bob | Alice | New York City | New York | +-----------+----------+---------------+----------+ Explanation: There is no address in the address table for the personId = 1 so we return null in their city and state. addressId = 1 contains information about the address of personId = 2.
Solutions
Solution 1: LEFT JOIN
We can use a left join to join the Person table with the Address table on the condition Person.personId = Address.personId, which will give us the first name, last name, city, and state of each person. If the address of a personId is not in the Address table, it will be reported as null.
-
import pandas as pd def combine_two_tables(person: pd.DataFrame, address: pd.DataFrame) -> pd.DataFrame: return pd.merge(left=person, right=address, how="left", on="personId")[ ["firstName", "lastName", "city", "state"] ] -
# Write your MySQL query statement below SELECT firstName, lastName, city, state FROM Person LEFT JOIN Address USING (personId);