MySQL query using INNER JOIN, Not returning what i'd expect.

Question

Hi,

I'm having trouble with the following query:

SELECT costingjobtimes.TimeStamp, costingdepartment.DeptDesc,  costingemployee.Name, costingjobtimes.TimeElapsed FROM costingemployee INNER JOIN (costingdepartment INNER JOIN costingjobtimes ON costingdepartment.DeptID = costingjobtimes.DeptID) ON costingemployee.EmployeeID = costingjobtimes.EmployeeID;

I would expect it to return every row in the costingjobtimes database however it's only returning 4 at current.

Basically, I have 3 tables. (costingjobtimes, costingdepartment, costingemployee) They are as follows:

mysql> DESCRIBE costingemployee
    -> ;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| EmployeeID | varchar(8) |      | PRI |         |       |
| Name       | text       |      |     |         |       |
+------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> DESCRIBE costingdepartment
    -> ;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| DeptID   | int(10) unsigned |      | PRI | 0       |       |
| DeptDesc | text             |      |     |         |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> DESCRIBE costingjobtimes
    -> ;
+-------------+------------------+------+-----+---------------------+-----------
-----+
| Field       | Type             | Null | Key | Default             | Extra
     |
+-------------+------------------+------+-----+---------------------+-----------
-----+
| id          | int(10) unsigned |      | PRI | NULL                | auto_incre
ment |
| EmployeeID  | text             |      |     |                     |
     |
| DeptID      | int(10) unsigned |      |     | 0                   |
     |
| JobNumber   | text             |      |     |                     |
     |
| TimeElapsed | decimal(10,5)    |      |     | 0.00000             |
     |
| TimeStamp   | datetime         |      |     | 0000-00-00 00:00:00 |
     |
| JobRef      | text             |      |     |                     |
     |
+-------------+------------------+------+-----+---------------------+-----------
-----+
7 rows in set (0.00 sec)

So all the query is supposed to do is return all the rows from costingjobtimes, but put in the employees name instead of EmployeeID and Department description instead of the DeptID. Any help would be great..

Thanks in advance,

Answer 1

I don't know if an Inner Join is really what you are looking for teishu. Maybe you should try a normal join.

For example:

select e.Name, d.DeptDesc, j.JobNumber, j.TimeElapsed, j.TimeStamp, j.JobRef
  from costingjobtimes as j
  join costingdepartment as d on d.DeptID = j.DeptId
  join costingemployee as e on e.EmployeeID = j.EmployeeID

This should give you a resultset with the information from the costingjobtimes table combined with the name of the employee and the description of the department.

Hope that helpes.