Skip to content
The Chat Archive
gpt-4-0314· OpenAI WildChat-4.8M (AI2)

write a sql query that lists the entire chain of foreign keyed tables starting from…

  1. User

    write a sql query that lists the entire chain of foreign keyed tables starting from tblEXAMPLE till the end of the chain of foreign keyed relations. I also want to make sure the results don't give me back duplicate table names.

  2. Assistant

    To write a SQL query that lists the entire chain of foreign keyed tables starting from tblEXAMPLE till the end of the chain of foreign keyed relations, we can use a recursive Common Table Expression (CTE). Here is the query:

    WITH RECURSIVE chain (tname, related_table) AS (
      SELECT
          table_name AS tname,
          referenced_table_name AS related_table
      FROM
          information_schema.key_column_usage
      WHERE
          table_name = 'tblEXAMPLE'
          AND referenced_table_name IS NOT NULL
      UNION ALL
      SELECT
          c.related_table AS tname,
          k.referenced_table_name AS related_table
      FROM
          chain c
          JOIN information_schema.key_column_usage k ON c.related_table = k.table_name
      WHERE
          k.referenced_table_name IS NOT NULL
    )
    , all_tables AS (
      SELECT tname FROM chain
      UNION
      SELECT related_table FROM chain
    )
    SELECT DISTINCT tname
    FROM all_tables;
    

    This query uses a recursive CTE to find all related tables starting from the 'tblEXAMPLE' table. It then combines the table names and related table names into a single column, 'tname', in the 'all_tables' CTE. Finally, it selects the distinct table names from the 'all_tables' to ensure that there are no duplicate table names in the result.