MySQL Forums
Forum List  »  Newbie

Automatic natural join on all foreign keys
Posted by: Micah Wedemeyer
Date: August 30, 2004 11:18AM

Is there any automated way (in MySQL 4.1.3) to get the results of a natural join on all foreign keys in a table? In other words, if I have a table T with foreign keys K1, K2, ..., Kn can I get a derived table that has T joined with all the these foreign tables? I am trying to do it now by parsing the CREATE TABLE statement to extract the foreign keys for a table, then using this to join T with these foreign tables. This works decently for 1 level deep (T and all its foreign keys), but I realized that it must work recursively (ie. I must join all the foreign tables on _their_ foreign keys). Even though I can theoretically do it with subqueries, it quickly gets to be a nightmare.

Note: I cannot hard-code any table-specific stuff since this is a generic function that is given a table name as input and generates a SELECT statement as output (or returns the records, if I cannot create a SELECT statement).

Options: ReplyQuote

Written By
Automatic natural join on all foreign keys
August 30, 2004 11:18AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.