0

I have a view table (let's call it view_table) composed of two tables (let's call them table_1 & table_2). Let's assume that table_1 has a foreign key on the field fk_field that references the table fk_table.

Is there anyway to directly query view_table in order get the reference table fk_table for the field fk_field?

I'm doing this from PHP so I was thinking of doing something along the lines of:

  1. Parse the VIEW_DEFINITION field from INFORMATION_SCHEMA.VIEWS in order to get a list of tables that constitute view_table (e.g. table_1 & table_2)
  2. For each of those tables, use the this method to get fk_table

Is there a better (i.e. simpler) approach?

EDIT

The view definition in this case, the field Container is a foreign key which references a table named Containers:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `LIMS_dev`.`DerivativeView` AS
    SELECT 
        `a`.`Derivative` AS `Derivative`,
        `a`.`Container` AS `Container`,
        `a`.`DerivativeTypeID` AS `DerivativeTypeID`,
        `a`.`StorageFormatID` AS `StorageFormatID`,
        `a`.`SourceSample` AS `SourceSample`,
        `a`.`Parent` AS `Parent`,
        `a`.`Date` AS `DerivativeDate`,
        `a`.`Active` AS `SampleActive`,
        `a`.`User` AS `User`,
        `a`.`Notes` AS `Notes`,
        `b`.`Shelf` AS `Shelf`,
        `b`.`Rack` AS `Rack`,
        `b`.`FreezerID` AS `FreezerID`,
        `b`.`Active` AS `BoxActive`,
        `b`.`Date` AS `ContainerDate`
    FROM
        (`LIMS_dev`.`Derivatives` `a`
        LEFT JOIN `LIMS_dev`.`Containers` `b` ON ((`a`.`Container` = `b`.`Container`)))
Community
  • 1
  • 1
Constantino
  • 2,243
  • 2
  • 24
  • 41
  • show your view_table code ... – ScaisEdge Nov 03 '15 at 17:35
  • You can parse the CREATE statements, and extract what you need from that, [maybe use this as a starting point](https://github.com/EVODelavega/mysql-diff) – Elias Van Ootegem Nov 03 '15 at 17:45
  • Is there a reason you can't use a simple `JOIN`? `SELECT * FROM view_table INNER JOIN fk_table USING (fk_field);`? – HPierce Nov 03 '15 at 17:49
  • perhaps I could, but what about the hoops needed just to find fk_table and the field fk_field? I can't assume to know those beforehand. – Constantino Nov 03 '15 at 17:53
  • 1
    @constantino: If you process the create table statement, you'll know the fk table and field, then you can query the DB for the create statement of that table, and work your way up to the point where you what all the tables you're interested in look like, what fields they contain and all that (that's what that mysql-diff thingy I linked to does anyway) – Elias Van Ootegem Nov 04 '15 at 09:28
  • yep that's what I ended up doing: – Constantino Nov 04 '15 at 21:43

0 Answers0