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.

Add sample table data for all tables involved, and specify the expected result - all as formatted text, not images. — jarlh
– jarlh, Commented Jan 25, 2018 at 7:39
With the query you have given and the description of the data, you should be expecting 9 records returned for 'ali'. If this is not what you are expecting, then show us what you are wanting to get. — ishando
– ishando, Commented Jan 25, 2018 at 7:42
i want to get 3 rows of ali. which is {pakistan, ceramic}, {india, iron} and {iran,plastic} — Haris Khan
– Haris Khan, Commented Jan 25, 2018 at 7:48

Professor Abronsius · Accepted Answer · 2018-01-25 08:56:47Z

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 | [email protected] | 0141 236 4523 | nowhere   |
|  2 | Boris  | [email protected]  | 0141 451 7845 | somewhere |
|  3 | Doris  | [email protected]  | 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 | [email protected] | 0141 236 4523 | nowhere   |    1 |    1 |   1  |    1 |    1 |    1 |    1 | ceramic |    1 | pakistan |
|  2 | Boris  | [email protected]  | 0141 451 7845 | somewhere |    2 |    2 |   2  |    2 |    2 |    2 |    2 | iron    |    2 | India    |
|  3 | Doris  | [email protected]  | 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 | [email protected] | 0141 236 4523 | nowhere   | ceramic  | pakistan |
|   2 | Boris  | [email protected]  | 0141 451 7845 | somewhere | iron     | India    |
|   3 | Doris  | [email protected]  | 0141 353 7845 | anywhere  | plastic  | Iran     |
+-----+--------+------------------+---------------+-----------+----------+----------+

mate, may you tell me how to create that block table +------+------+ ? sry OOT.
open the command prompt, type mysql -u <USER> -p <DBNAME> - <ENTER>, then describe <TABLE>; ~ items within <> to be replaced with real values
thank you very much. i will try it. PS: this is really helpfull for me to draw table structure example in SO
Example: mysql -u root -p inventory <ENTER> ~ leaving the -p with no password on the command line means you get prompted for the password after pressing ENTER. In this the database is called inventory
no the problem is i am getting 9 rows of harris vendors. but I want 3 rows, its showing the result for harris like this: {ceramic,pakistan}, {iron,pakistan},{plastic,pakistan} ,{ceramic,india}, {iron,india},{plastic,india} and then 3 rows for iran

Collectives™ on Stack Overflow

how to fetch data from multiple tables

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related