175. Combine Two Tables – Leetcode Solutions

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.

Examples:

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.

Solution in Pandas

To solve this problem in Pandas, we will perform a left join between the Person and Address tables on the personId column. This type of join ensures that all entries from the Person table are included in the result, even if there is no matching entry in the Address table. For persons without a matching address, the city and state fields will be filled with null values.

Step-by-step solution:

1. Import the necessary library.
2. Define the function combine_two_tables that takes two DataFrames (person and address) as input.
3. Use the merge function from Pandas to perform the left join.
4. Select and reorder the required columns in the final output.

import pandas as pd

def combine_two_tables(person: pd.DataFrame, address: pd.DataFrame) -> pd.DataFrame:
    # Perform a left join between person and address on personId
    result = pd.merge(person, address, how='left', on='personId')
    
    # Select the required columns and their order
    result = result[['firstName', 'lastName', 'city', 'state']]
    
    return result

Explanation:

1. Import pandas library: We need Pandas for data manipulation and analysis.
2. Function Definition: The function combine_two_tables is defined to accept two DataFrame inputs: person and address.
3. Left Join: pd.merge(person, address, how='left', on='personId') is used to merge the person and address DataFrames. The how='left' parameter ensures that all records from the person DataFrame are included in the resulting DataFrame, and if there is no matching personId in the address DataFrame, the corresponding city and state values will be NaN (which will be interpreted as null).
4. Select and Reorder Columns: The result DataFrame is then filtered to include only the firstName, lastName, city, and state columns in the specified order.

This function can be used to combine the two tables as described, ensuring that all persons are included in the final result, with null values for missing addresses.

Solution in MySQL

-- Select the necessary columns from the Person table and corresponding columns from the Address table
SELECT
    p.firstName,
    p.lastName,
    a.city,
    a.state
FROM
    Person p
-- Perform a LEFT JOIN with the Address table on the personId column
LEFT JOIN
    Address a ON p.personId = a.personId;