how to fetch data from multiple tables

Question

So I normalize my database and it seems quite complicated for me to fetch or update records in tables. I have 5 tables ( details, country, materials, vendor_countries, vendor_materials). Actually "details" is my main table.

• country table have two column (id, name).
• materials table also have two column(id, name).
• details table have personnel information about vendors (name, email, phone, address).

A vendor could have multiple countries and materials so I made two more tables vendor_countries and vendor_materials and their column are (did,cid), (did,mid). "did" is the id of each vendor coming from details table and cid,mid is the country id and material id coming from country and material table.

Now I want to fetch vendors along with their countries and materials.

here is my details table, vendor_countries and vendor_materials tables

so far I made a query which is shown below:

select dt.vendor,
       dt.email,
       dt.address,
       c.country,
       m.material,
       c.country
  from country c
       inner join vendor_countries vc on (c.id = vc.cid)
       right join details dt on (dt.id = vc.did)
       left join vendor_materials vm on dt.id = vm.did
       left join material m on vm.mid = m.id

and i am getting results like this:

which is not right it should be in 3 rows because "ali" vendor works in 3 countries and 3 materials. I am looking for some smart solution which also not slow down my page. Thanks in advance.

Answer 1

In the absence of any response regarding table schema I tried to re-create the tables based upon the descriptions above -prefixing each table with v_ so I could group the tables in this db and referencing did as vid ( vendor id ) and populating with what I believed to be the sample data from the question

mysql> describe v_country;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+


mysql> describe v_details;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vendor  | varchar(50)      | YES  |     | NULL    |                |
| email   | varchar(50)      | YES  |     | NULL    |                |
| phone   | varchar(50)      | YES  |     | NULL    |                |
| address | varchar(50)      | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+


mysql> describe v_materials;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+


mysql> describe v_vendor_materials;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vid   | int(10) unsigned | NO   | MUL | 0       |                |
| mid   | int(10) unsigned | NO   | MUL | 0       |                |
+-------+------------------+------+-----+---------+----------------+


mysql> describe v_vendor_countries;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vid   | int(10) unsigned | NO   | MUL | 0       |                |
| mid   | int(10) unsigned | NO   | MUL | 0       |                |
+-------+------------------+------+-----+---------+----------------+






mysql> select * from v_country;
+----+----------+
| id | name     |
+----+----------+
|  1 | pakistan |
|  2 | India    |
|  3 | Iran     |
+----+----------+


mysql> select * from v_details;
+----+--------+------------------+---------------+-----------+
| id | vendor | email            | phone         | address   |
+----+--------+------------------+---------------+-----------+
|  1 | harris | harris@gmail.com | 0141 236 4523 | nowhere   |
|  2 | Boris  | boris@gmail.com  | 0141 451 7845 | somewhere |
|  3 | Doris  | doris@gmail.com  | 0141 353 7845 | anywhere  |
+----+--------+------------------+---------------+-----------+


mysql> select * from v_materials;
+----+---------+
| id | name    |
+----+---------+
|  1 | ceramic |
|  2 | iron    |
|  3 | plastic |
+----+---------+


mysql> select * from v_vendor_materials;
+----+-----+-----+
| id | vid | mid |
+----+-----+-----+
|  1 |   1 |   1 |
|  2 |   2 |   2 |
|  3 |   3 |   3 |
+----+-----+-----+


mysql> select * from v_vendor_countries;
+----+-----+-----+
| id | vid | mid |
+----+-----+-----+
|  1 |   1 |   1 |
|  2 |   2 |   2 |
|  3 |   3 |   3 |
+----+-----+-----+





mysql> select * from v_details d
          left outer join v_vendor_materials vm on vm.vid=d.id
          left outer join v_vendor_countries vc on vc.vid=d.id
          left outer join v_materials m on m.id=vm.id
          left outer join v_country c on c.id=vc.id;

+----+--------+------------------+---------------+-----------+------+------+------+------+------+------+------+---------+------+----------+
| id | vendor | email            | phone         | address   | id   | vid  | mid  | id   | vid  | mid  | id   | name    | id   | name     |
+----+--------+------------------+---------------+-----------+------+------+------+------+------+------+------+---------+------+----------+
|  1 | harris | harris@gmail.com | 0141 236 4523 | nowhere   |    1 |    1 |   1  |    1 |    1 |    1 |    1 | ceramic |    1 | pakistan |
|  2 | Boris  | boris@gmail.com  | 0141 451 7845 | somewhere |    2 |    2 |   2  |    2 |    2 |    2 |    2 | iron    |    2 | India    |
|  3 | Doris  | doris@gmail.com  | 0141 353 7845 | anywhere  |    3 |    3 |   3  |    3 |    3 |    3 |    3 | plastic |    3 | Iran     |
+----+--------+------------------+---------------+-----------+------+------+------+------+------+------+------+---------+------+----------+

or, a more selective query

select 
    d.`id` as `vid`,
    d.`vendor`,
    d.`email`,
    d.`phone`,
    d.`address`,
    m.`name` as `material`,
    c.`name` as `country`
    from v_details d
    left outer join v_vendor_materials vm on vm.vid=d.id
    left outer join v_vendor_countries vc on vc.vid=d.id
    left outer join v_materials m on m.id=vm.id
    left outer join v_country c on c.id=vc.id;


+-----+--------+------------------+---------------+-----------+----------+----------+
| vid | vendor | email            | phone         | address   | material | country  |
+-----+--------+------------------+---------------+-----------+----------+----------+
|   1 | harris | harris@gmail.com | 0141 236 4523 | nowhere   | ceramic  | pakistan |
|   2 | Boris  | boris@gmail.com  | 0141 451 7845 | somewhere | iron     | India    |
|   3 | Doris  | doris@gmail.com  | 0141 353 7845 | anywhere  | plastic  | Iran     |
+-----+--------+------------------+---------------+-----------+----------+----------+