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)
) TYPE=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)
) TYPE=MyISAM;

INSERT INTO REL_persons VALUES (11, ‘Marc’, ‘S’, ”);
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)
) TYPE=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
* in Links, for “town_code” choose “REL_towns->code”
* in Links, for “country_code” choose “REL_countries->country_code”
* on table “REL_towns” click Structure, then Relation view
* in “Choose field to display”, choose “description”
* repeat the two previous steps for table “REL_countries”

Then test like this:

* Click on your db name in the left frame
* Choose “Query”
* Use tables: persons, towns, countries
* Click “Update query”
* In the fields 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”