如何从Fred-Family的ID获取对应Fred的ID？（SQL查询求助）

Got it, let's work through this SQL problem together based on your approach. First, I'll assume your table is named entities with two key columns: id (the unique identifier) and name (the entity name field). Adjust the table/column names to match your actual schema!

Option 1: Subquery (Follows Your Exact Workflow) #

This directly maps to the two steps you outlined: first fetch the name for ID 8, then strip the " - Family" suffix to find the matching "Fred" entry.

SELECT id
FROM entities
WHERE name = (
    -- Step 1: Get the name for ID 8, then remove the " - Family" suffix
    SELECT REPLACE(name, ' - Family', '')
    FROM entities
    WHERE id = 8
);

Option 2: Self-Join (More Efficient for Large Datasets) #

If you're working with a large table, a self-join can be more efficient than nested subqueries. It links the "Fred - Family" entry directly to the "Fred" entry in one pass:

SELECT e2.id AS fred_id
FROM entities e1
JOIN entities e2 
  ON e2.name = REPLACE(e1.name, ' - Family', '')
WHERE e1.id = 8;

Edge Case & Database-Specific Tweaks #

• If there's a chance multiple entries match the stripped name (unlikely in your business scenario), add LIMIT 1 (for MySQL/PostgreSQL) or TOP 1 (for SQL Server) to get a single result.
• If your database uses different string functions:
  • MySQL/PostgreSQL: REPLACE() works as shown, or use SUBSTRING_INDEX(name, ' - Family', 1) to safely grab everything before the suffix (even if the name has extra hyphens).
  • SQL Server: REPLACE() is still valid, or use LEFT(name, CHARINDEX(' - Family', name) - 1) to extract the prefix.

内容的提问来源于stack exchange，提问作者jose