How can I use the relation table in Query-by-example? ¶

Here is an example with the tables persons, towns and countries, all located in the database "mydb". If you don't have a pma__relation table, create it as explained in the configuration section. Then create the example tables:

CREATE TABLE REL_countries ( country_code char(1) NOT NULL default '', description varchar(10) NOT NULL default '', PRIMARY KEY (country_code) ) ENGINE=MyISAM;  INSERT INTO REL_countries VALUES ('C', 'Canada');  CREATE TABLE REL_persons ( id tinyint(4) NOT NULL auto_increment, person_name varchar(32) NOT NULL default '', town_code varchar(5) default '0', country_code char(1) NOT NULL default '', PRIMARY KEY (id) ) ENGINE=MyISAM;  INSERT INTO REL_persons VALUES (11, 'Marc', 'S', 'C'); INSERT INTO REL_persons VALUES (15, 'Paul', 'S', 'C');  CREATE TABLE REL_towns ( town_code varchar(5) NOT NULL default '0', description varchar(30) NOT NULL default '', PRIMARY KEY (town_code) ) ENGINE=MyISAM;  INSERT INTO REL_towns VALUES ('S', 'Sherbrooke'); INSERT INTO REL_towns VALUES ('M', 'Montréal'); 

To setup appropriate links and display information:

on table "REL_persons" click Structure, then Relation view
for "town_code", choose from dropdowns, "mydb", "REL_towns", "code" for foreign database, table and column respectively
for "country_code", choose from dropdowns, "mydb", "REL_countries", "country_code" for foreign database, table and column respectively
on table "REL_towns" click Structure, then Relation view
in "Choose column to display", choose "description"
repeat the two previous steps for table "REL_countries"

Then test like this:

Click on your db name in the navigation panel
Choose "Query"
Use tables: persons, towns, countries
Click "Update query"
In the columns row, choose persons.person_name and click the "Show" tickbox
Do the same for towns.description and countries.descriptions in the other 2 columns
Click "Update query" and you will see in the query box that the correct joins have been generated
Click "Submit query"