Getting referenced tables in MySQL

Given

TABLE A
id    field1   field2  field3
--------------------------
1     foo      1        2
2     bar      1        2
3     grok     2        3

TABLE B
id    name
-------------
1     mike
2     test

TABLE C
id   address
-------------
1    Nowhere
2    Somewhere
3    Everyhwere

field2 is a foreign key to TABLE B field3 is a foreign key to TABLE C

Is there a way via SQL to get the data below generically(it can apply to any table)

1. Table name of the referenced tables(TABLE B and C).
2. Referenced Row data in the referenced tables(TABLE B and 开发者_运维知识库C) of the foreign key(s)

I am not sure but information schema can help with this one?

Table name of the referenced tables(TABLE B and C).

Not sure may be you have to save this information in column to get it.

Referenced Row data in the referenced tables(TABLE B and C) of the foreign key(s)

You can try:

select * from TableB where id in (select field2 from TableA);

select * from TableC where id in (select field3 from TableA);

List of SELECT statements you could get with next query -

SELECT CONCAT('SELECT * FROM ', referenced_table_schema, '.', referenced_table_name, ' JOIN ', table_schema, '.', table_name, ' ON ', referenced_table_schema, '.', referenced_table_name, '.', referenced_column_name, '=', table_schema, '.', table_name, '.', column_name) selects
FROM
  information_schema.key_column_usage
WHERE
  referenced_table_schema = 'database_name'
  AND (referenced_table_name = 'table_b'
  OR referenced_table_name = 'table_c')
GROUP BY
  constraint_schema
, constraint_name;

Result:

-------------------------------------------------------------
SELECT * FROM database1.table_b JOIN database1.TABLE_A ON database1.table_b.id=database1.TABLE_A.field2
-------------------------------------------------------------
SELECT * FROM database1.table_c JOIN database1.TABLE_A ON database1.table_c.id=database1.TABLE_A.field3

It works for foreign keys with one column, but I think it can be rewrited to support composite indexes. SELECT statements can be executed with prepared statements.