MySQL db连接查询

Question

我正在Mysql中使用一个雇员数据库。我的数据库包含以下表

    mysql> describe edept;     
+-------+-------------+------+-----+---------+-------+  
| Field | Type        | Null | Key | Default | Extra |  
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| dept  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


mysql>describe esal;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| basic | int(11) | NO   |     | NULL    |       |
| pf    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> describe edesig;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| desig | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


mysql> select * from edetails inner join edept on edetails.dept=edept.id;
+----+--------+-----+------+-------+-------+----+----+------------------+
| id | name   | age | dept | desig | basic | pf | id | dept             |
+----+--------+-----+------+-------+-------+----+----+------------------+
|  1 | swetha |  21 |    3 |     2 |     2 |  2 |  3 | Business Process |
+----+--------+-----+------+-------+-------+----+----+------------------+


mysql> describe edetails;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| dept  | int(11)     | NO   | MUL | NULL    |       |
| desig | int(11)     | YES  | MUL | NULL    |       |
| basic | int(11)     | NO   | MUL | NULL    |       |
| pf    | int(11)     | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+

我必须分别从表、edesig.desig、esal.basic、esal.pf中获取edept.dept、dept、basic、pf的值。

I used foreign keys for all the   fields for which i have to retrieve values from other tables.And i tried a sample inner join query. but i got the output as follows:  

 mysql> select * from edetails inner join edept on edetails.dept=edept.id;
    +----+--------+-----+------+-------+-------+----+----+------------------+
    | id | name   | age | dept | desig | basic | pf | id | dept             |
    +----+--------+-----+------+-------+-------+----+----+------------------+
    |  1 | swetha |  21 |    3 |     2 |     2 |  2 |  3 | Business Process |
    +----+--------+-----+------+-------+-------+----+----+------------------+

My edept table contains the following:


 mysql> select * from edept;
+----+------------------+
| id | dept             |
+----+------------------+
|  3 | Business Process |
+----+------------------+



How can i eliminate duplicate columns. i need the value "business process" in the dept field of the edept table

Answer 1

试一试：

    select 
    edetails.id,
    edetails.name,
    edetails.age,
    edetails.dept, 
    edesig.desig,
    edetails.basic,
    edetails.pf,
    edept.dept

     from edetails 
        inner join edept on edetails.dept=edept.id
INNER JOIN edesig on edesig.id=edetails.desig