如何查询关系型数据库中两张表的关联路径及关联键

Alright, let's figure out how to solve this problem. Since you don't have access to the database diagram and need to batch check relationship chains between table pairs (like A and D) along with the required join columns, we can leverage the database's built-in system catalogs to retrieve this metadata automatically. Below are tailored solutions for the most common database systems:

MySQL stores relationship metadata in the information_schema database. This recursive CTE will trace the path from table A to D, then generate the full JOIN query:

WITH RECURSIVE table_relationships AS (
    -- Start with direct relationships from table A
    SELECT 
        tc.table_name AS from_table,
        kcu.column_name AS from_column,
        ccu.table_name AS to_table,
        ccu.column_name AS to_column,
        1 AS depth
    FROM 
        information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu 
            ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage ccu 
            ON tc.constraint_name = ccu.constraint_name
    WHERE 
        tc.constraint_type = 'FOREIGN KEY'
        AND kcu.table_name = 'A' -- Replace with your starting table
    UNION ALL
    -- Recursively follow relationships until we reach table D
    SELECT 
        tr.from_table,
        tr.from_column,
        ccu.table_name AS to_table,
        ccu.column_name AS to_column,
        tr.depth + 1 AS depth
    FROM 
        table_relationships tr
        JOIN information_schema.table_constraints tc 
            ON tr.to_table = kcu.table_name
        JOIN information_schema.key_column_usage kcu 
            ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage ccu 
            ON tc.constraint_name = ccu.constraint_name
    WHERE 
        tc.constraint_type = 'FOREIGN KEY'
        AND tr.to_table != 'D' -- Stop when we hit the target table
)
-- Build the final JOIN query string
SELECT 
    CONCAT('SELECT A.*, D.* FROM A ', 
           GROUP_CONCAT(
               CONCAT('JOIN ', tr.to_table, ' ON ', tr.from_table, '.', tr.from_column, ' = ', tr.to_table, '.', tr.to_column)
               ORDER BY depth SEPARATOR ' '
           ),
           ' JOIN D ON ', last_tr.to_table, '.', last_tr.to_column, ' = D.', (SELECT column_name FROM information_schema.columns WHERE table_name='D' AND column_key='PRI')
    ) AS full_join_query
FROM 
    table_relationships tr
    JOIN (
        SELECT from_table, from_column, to_table, to_column 
        FROM table_relationships 
        WHERE to_table = 'D'
    ) last_tr ON tr.depth = last_tr.depth - 1
WHERE 
    tr.depth = (SELECT MAX(depth) FROM table_relationships WHERE to_table = 'D') - 1
GROUP BY 
    tr.from_table, tr.from_column;

SQL Server uses system views like sys.tables and sys.foreign_keys to store relationship data. Here's the equivalent recursive query:

WITH RECURSIVE table_relationships AS (
    -- Start with direct relationships from table A
    SELECT 
        OBJECT_NAME(fk.parent_object_id) AS from_table,
        col_parent.name AS from_column,
        OBJECT_NAME(fk.referenced_object_id) AS to_table,
        col_ref.name AS to_column,
        1 AS depth
    FROM 
        sys.foreign_keys fk
        JOIN sys.foreign_key_columns fkc 
            ON fk.object_id = fkc.constraint_object_id
        JOIN sys.columns col_parent 
            ON fkc.parent_object_id = col_parent.object_id 
            AND fkc.parent_column_id = col_parent.column_id
        JOIN sys.columns col_ref 
            ON fkc.referenced_object_id = col_ref.object_id 
            AND fkc.referenced_column_id = col_ref.column_id
    WHERE 
        OBJECT_NAME(fk.parent_object_id) = 'A' -- Replace with your starting table
    UNION ALL
    -- Recursively follow relationships until we reach table D
    SELECT 
        tr.from_table,
        tr.from_column,
        OBJECT_NAME(fk.referenced_object_id) AS to_table,
        col_ref.name AS to_column,
        tr.depth + 1 AS depth
    FROM 
        table_relationships tr
        JOIN sys.foreign_keys fk 
            ON tr.to_table = OBJECT_NAME(fk.parent_object_id)
        JOIN sys.foreign_key_columns fkc 
            ON fk.object_id = fkc.constraint_object_id
        JOIN sys.columns col_ref 
            ON fkc.referenced_object_id = col_ref.object_id 
            AND fkc.referenced_column_id = col_ref.column_id
    WHERE 
        OBJECT_NAME(fk.referenced_object_id) != 'D' -- Stop when we hit the target table
)
-- Build the final JOIN query string
SELECT 
    CONCAT('SELECT A.*, D.* FROM A ',
           STRING_AGG(
               CONCAT('JOIN ', tr.to_table, ' ON ', tr.from_table, '.', tr.from_column, ' = ', tr.to_table, '.', tr.to_column),
               ' '
           ) WITHIN GROUP (ORDER BY depth),
           ' JOIN D ON ', last_tr.to_table, '.', last_tr.to_column, ' = D.', (SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('D') AND is_identity = 1 OR (SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID('D') AND column_id IN (SELECT column_id FROM sys.index_columns WHERE object_id = OBJECT_ID('D') AND index_id = 1)) = 1)
    ) AS full_join_query
FROM 
    table_relationships tr
    JOIN (
        SELECT from_table, from_column, to_table, to_column 
        FROM table_relationships 
        WHERE to_table = 'D'
    ) last_tr ON tr.depth = last_tr.depth - 1
WHERE 
    tr.depth = (SELECT MAX(depth) FROM table_relationships WHERE to_table = 'D') - 1
GROUP BY 
    tr.from_table, tr.from_column;

PostgreSQL uses information_schema tables similar to MySQL, with a few differences in metadata access. Here's the recursive query for PostgreSQL:

WITH RECURSIVE table_relationships AS (
    -- Start with direct relationships from table A
    SELECT 
        tc.table_name AS from_table,
        kcu.column_name AS from_column,
        ccu.table_name AS to_table,
        ccu.column_name AS to_column,
        1 AS depth
    FROM 
        information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu 
            ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage ccu 
            ON tc.constraint_name = ccu.constraint_name
    WHERE 
        tc.constraint_type = 'FOREIGN KEY'
        AND kcu.table_name = 'A' -- Replace with your starting table
    UNION ALL
    -- Recursively follow relationships until we reach table D
    SELECT 
        tr.from_table,
        tr.from_column,
        ccu.table_name AS to_table,
        ccu.column_name AS to_column,
        tr.depth + 1 AS depth
    FROM 
        table_relationships tr
        JOIN information_schema.table_constraints tc 
            ON tr.to_table = kcu.table_name
        JOIN information_schema.key_column_usage kcu 
            ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage ccu 
            ON tc.constraint_name = ccu.constraint_name
    WHERE 
        tc.constraint_type = 'FOREIGN KEY'
        AND tr.to_table != 'D' -- Stop when we hit the target table
)
-- Build the final JOIN query string
SELECT 
    CONCAT('SELECT A.*, D.* FROM A ',
           STRING_AGG(
               CONCAT('JOIN ', tr.to_table, ' ON ', tr.from_table, '.', tr.from_column, ' = ', tr.to_table, '.', tr.to_column),
               ' '
           ORDER BY depth),
           ' JOIN D ON ', last_tr.to_table, '.', last_tr.to_column, ' = D.', (SELECT column_name FROM information_schema.columns WHERE table_name='D' AND column_key='PRI')
    ) AS full_join_query
FROM 
    table_relationships tr
    JOIN (
        SELECT from_table, from_column, to_table, to_column 
        FROM table_relationships 
        WHERE to_table = 'D'
    ) last_tr ON tr.depth = last_tr.depth - 1
WHERE 
    tr.depth = (SELECT MAX(depth) FROM table_relationships WHERE to_table = 'D') - 1
GROUP BY 
    tr.from_table, tr.from_column;

Notes: #

• Replace 'A' and 'D' with your actual table names in each query.
• If multiple relationship paths exist between A and D (e.g., A→B→D and A→C→D), the query will return all valid JOIN statements.
• This base version handles single-column primary/foreign keys. For composite keys, you'll need to extend the query to aggregate multiple join columns into a single condition.

内容的提问来源于stack exchange，提问作者Make an Impact