How to Select the Last Records in a One-To-Many Relationship Using SQL Join

SQL (Structured Query Language) is a powerful tool used to communicate with and manage databases. Think of a database as a digital filing cabinet where you store information. It helps you interact with this filing cabinet by letting you perform tasks like adding, updating, retrieving, and deleting data. It’s like a universal language for databases, allowing you to ask questions like “What are all the customers’ names?” or “How many products do we have in stock?” It is structured in a way that makes it easy for both humans and computers to understand, making it essential for anyone dealing with data management or analysis.

In this article, we will see, How to select the last records in a one-to-many relationship using SQL join.

Relationships in SQL

Relationships in SQL refer to the associations or connections between tables in a relational database. These relationships are established using foreign keys, which are columns in a table that refer to the primary key in another table. Relationships help organize and structure data, allowing for efficient data retrieval and maintaining data integrity.

There are different types of relationships: one-to-one, one-to-many, many-to-many, and self-referencing.

Relationships in SQL

1. One-to-One Relationship

Definition: Each record in Table A is associated with one and only one record in Table B, and vice versa.

Setup: Include a foreign key in one of the tables that references the primary key of the other table.

For example: Tables users and user_profiles, where each user has a single corresponding profile.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT UNIQUE,
    profile_data VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Output:

one-to-one relatonship

2. One-to-Many Relationship

Definition: Each record in Table A can be associated with multiple records in Table B, but each record in Table B is associated with only one record in Table A.

Setup: Include a foreign key in the “many” side table (Table B) that references the primary key of the “one” side table (Table A).

For example: Tables departments and employees, where each department can have multiple employees, but each employee belongs to one department.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Output:

One-to-many relationship

3. Many-to-Many Relationship

Definition: Each record in Table A can be associated with multiple records in Table B, and vice versa.

Setup: Create an intermediate table (also known as a junction or linking table) that contains foreign keys referencing both related tables.

For example: Tables students and courses, where each student can enroll in multiple courses, and each course can have multiple students.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Output:

Many-to-many relationship

4. Self-Referencing Relationship

Definition: A table has a foreign key that references its primary key.

Setup: Include a foreign key column in the same table that references its primary key.

For example : A table employees with a column manager_id referencing the same table’s employee_id.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Output:

Self-Referencing Relationship

SQL joins

A join is a mechanism that combines rows from two or more tables based on a related column between them. The purpose of using joins is to retrieve data from multiple tables in a single result set. The common columns used for joining tables are usually primary and foreign keys. There are several types of joins in SQL

SQL joins

consider the following database,

Database

1. INNER JOIN

Returns only the rows where there is a match in both tables based on the specified condition.

Syntax:

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Example: in above database,

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Output:

INNER JOIN output

Explanation:

The SQL query selects employee_id, employee_name, and department_name from employees and departments tables, joining them on department_id. It retrieves information about employees and their corresponding department names.

2. LEFT (OUTER) JOIN

Returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

Syntax:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Example: in above database,

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Output:

LEFT (OUTER) JOIN output

Explanation: The SQL query uses a LEFT JOIN to retrieve employee_id, employee_name, and department_name from employees and departments, showing all employees and their associated department names, including those without a match.

3. RIGHT (OUTER) JOIN

Returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Syntax:

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Example: in above database,

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Output:

RIGHT (OUTER) JOIN output

Explanation: The SQL query employs a RIGHT JOIN to fetch employee_id, employee_name, and department_name from employees and departments, displaying all departments and their corresponding employees, including unmatched departments without employees.

4. FULL (OUTER) JOIN

Returns all rows when there is a match in either the left or the right table. If there is no match, NULL values are returned for columns from the non-matching table.

Syntax:

SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

Example: in above database,

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

Output:

FULL (OUTER) JOIN

Explanation: The SQL query employs a FULL JOIN to retrieve employee_id, employee_name, and department_name from employees and departments, displaying all records from both tables, matching on department_id and including unmatched rows from both tables.

Now, when we have learned what is relationships in MySQL and what are MySQL joins. Let us see

How to Select the Last Records

Here are several possible ways to accomplish this task:

consider the following database,

Database

1. Using Subquery with LIMIT and ORDER BY

Example: in above database,

SELECT *
FROM parent p
JOIN child c ON p.parent_id = c.parent_id
WHERE c.child_id = (
    SELECT child_id
    FROM child
    WHERE parent_id = p.parent_id
    ORDER BY created_at DESC
    LIMIT 1
);

Output:

Using Subquery with LIMIT and ORDER BY output

Explanation: The above SQL query retrieves records from a parent-child relationship in a SQL database. It employs a subquery to find the latest child record for each parent based on the ‘created_at‘ timestamp. The main query then joins the parent and child tables, filtering the results to include only the rows where the child ID matches the one obtained from the subquery. This effectively selects the most recent child record for each parent in a one-to-many relationship, ensuring only the latest child entry per parent is included in the final result set.

2. Using Subquery with MAX

Example: in above database,

SELECT p.*, c.*
FROM parent p
JOIN child c ON p.parent_id = c.parent_id
JOIN (
    SELECT parent_id, MAX(created_at) AS max_created_at
    FROM child
    GROUP BY parent_id
) AS latest_child ON c.parent_id = latest_child.parent_id
               AND c.created_at = latest_child.max_created_at;

Output:

Using Subquery with MAX output

Explanation: The given SQL query retrieves the latest records from a one-to-many relationship between a “parent” and “child” table in a SQL database. It does so by joining the “parent” and “child” tables based on the common parent_id, and additionally using a subquery to identify the maximum created_at timestamp for each parent_id in the “child” table. The main join condition includes a comparison with the latest_child subquery, ensuring that only the rows with the maximum created_at for each parent_id are selected. The result set includes all columns from both the “parent” and “child” tables for the latest records in the one-to-many relationship.

3. Using Correlated Subquery

Example: in above database,

 SELECT p.*, c.*
FROM parent p
JOIN child c ON p.parent_id = c.parent_id
WHERE c.created_at = (
    SELECT MAX(created_at)
    FROM child
    WHERE parent_id = p.parent_id
);

Output:

Using correlated subquery

Explanation: In the given SQL query using a correlated subquery, it selects records from a one-to-many relationship between a parent and child table. The query retrieves all columns from the parent and child tables for rows where the child’s creation timestamp is the maximum within each group of children sharing the same parent. Essentially, it returns the latest child record for each parent based on the “created_at” timestamp, providing a concise way to obtain the most recent child entry for each parent in the relationship

4. Using ROW_NUMBER() Window Function

Example: in above database,

WITH RankedChild AS (
    SELECT
        p.*,
        c.*,
        ROW_NUMBER() OVER (PARTITION BY p.parent_id ORDER BY c.created_at DESC) AS rn
    FROM parent p
    JOIN child c ON p.parent_id = c.parent_id
)
SELECT *
FROM RankedChild
WHERE rn = 1;

Output:

Using ROW_NUMBER() Window Function output

Explanation: In the given example SQL query, the ROW_NUMBER() window function is used to assign a unique row number to each record in the result set based on the descending order of the “created_at” column within each partition defined by the “parent_id.” The query joins the “parent” and “child” tables on the “parent_id” and selects all columns from both tables along with the calculated row number. The final output, obtained by filtering rows where the row number (rn) is equal to 1, retrieves the latest records for each parent in a one-to-many relationship, effectively selecting the most recently created child record for each parent.

5. Using LEFT JOIN and IS NULL

Example: in above database,

SELECT p.*, c.*
FROM parent p
LEFT JOIN child c ON p.parent_id = c.parent_id
LEFT JOIN child c2 ON p.parent_id = c2.parent_id AND c.created_at < c2.created_at
WHERE c2.parent_id IS NULL;

Output:

Using LEFT JOIN and IS NULL output

Explanation: The given SQL query retrieves records from a one-to-many relationship between a “parent” table (denoted as p) and a “child” table (denoted as c). It uses a LEFT JOIN to match rows from the parent table with corresponding rows in the child table based on the parent_id. Additionally, it employs a self-join on the child table (c2) to identify the latest records by comparing their created_at timestamps. The WHERE clause ensures that only rows with no later child records (c2.parent_id IS NULL) are included, effectively selecting the latest records for each parent in the one-to-many relationship.

Conclusion

In conclusion, when selecting the last records in a one-to-many relationship using SQL join, there are several approaches available. You can use subqueries with `LIMIT` and `ORDER BY`, leverage `MAX` in subqueries, employ correlated subqueries, utilize the `ROW_NUMBER()` window function, or employ a `LEFT JOIN` with a condition. Each method aims to retrieve the latest records from the related table, whether it’s based on timestamps, maximum values, or window functions. The choice depends on your specific needs, database structure, and performance considerations.