Скачать или смотреть Efficiently Fetching Rows with Foreign Keys in MySQL

Discover how to retrieve posts and polls from a feed table containing foreign keys to efficiently display your data in MySQL.
---
This video is based on the question https://stackoverflow.com/q/68866232/ asked by the user 'CapBul' ( https://stackoverflow.com/u/14231546/ ) and on the answer https://stackoverflow.com/a/68866649/ provided by the user 'Joel Coehoorn' ( https://stackoverflow.com/u/3043/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to return rows when you have foreign keys to two different tables

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Efficiently Fetching Rows with Foreign Keys in MySQL

When working with relational databases, it’s common to encounter situations where you have foreign keys that link multiple tables. A typical scenario might involve retrieving data from different tables based on a condition, which can be quite tricky. In this guide, we will address a common problem encountered in MySQL – how to fetch rows from tables that are linked by foreign keys, specifically when you have entries in two different tables.

Problem Overview

Imagine you have three tables in your database: feed, posts, and polls. The feed table contains foreign keys that refer to both the posts and polls tables. The structure of your tables is as follows:

feed

id (auto_increment)

post_id (foreign key to posts.id)

poll_id (foreign key to polls.id)

posts

id (auto_increment)

body (text of the post)

polls

id (auto_increment)

question (text of the poll)

The challenge here lies in the requirement that depending on the type of entry, either the post_id or poll_id will be null, which means that a straightforward JOIN operation won’t work as expected when trying to fetch entries from both the posts and polls into the same result set.

The Solution

To solve this problem, you can utilize a combination of JOIN and a UNION statement. Here’s a step-by-step breakdown of how to construct your SQL query for fetching data from both tables based on the existing foreign keys.

Step 1: Create a Union of Both Tables

First, you create a subquery that combines the relevant fields from both the posts and polls tables. This is achieved using the UNION ALL, which allows you to stack the contents of both tables into a single result set.

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Join the Feed Table

Next, you'll join this unioned result back to the feed table. You will need to handle the possibility of having null values for either post_id or poll_id. The COALESCE function helps by returning the first non-null value among its arguments.

Complete SQL Query

Here’s how the complete SQL statement looks:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

SELECT Statement: We select the body of the post or poll and the user ID.

JOIN Clause: We join the feed table with the union of posts and polls.

COALESCE Function: This function is key; it checks if post_id is null, if so, it uses poll_id instead, ensuring we retrieve the correct data.

ORDER BY: Ordering the final results by feed.id in descending order gives you the latest entries first.

Final Thoughts

By using the approach outlined above, you can effectively fetch rows from your feed table that reference both posts and polls without encountering empty results. Although this method may seem complex at first, it effectively handles the intricacies of foreign key relationships in SQL.

Note: If you find yourself needing to use such joins frequently, it might be worth reconsidering the database schema to simplify future queries.

With this guide, you should be able to tackle similar challenges in your database projects. Happy querying!