I've 3 tables. service_robot_errorservice consists of an overseas key that's primary type in the service table. robot_error doesn't have ore give any foreign secrets.

TABLE: robot_error
| id | date | stop_time | start_time | robot | position | error_code | observation | solution | downtime | logged_by |
| 1 | 02.01.2012 | 14:51:31 | 14:52:00 | 20 | 25/7 | 214 | X dir | Rob off | 29 | XXX |

TABLE: service
| service_id | service_date | service_module | service_user | service_comment | service_type |
| 1 | 13.01.2012 | Robot | XXX | Test service | Errorservice |

TABLE: service_robot_errorservice
| errorservice_id | service_id | errorservice_robot | errorservice_tracksensor | errorservice_gripper |
| 5 | 1 | 54 | OK | OK |

Whenever a user looks for a robot (eks 16) i would like the end result to become every instance in robot_error orderd by date And when the robot has service registered this instance ought to be proven within the result.

Example:

robot_error instance
robot_error instance
robot_error instance
service instance
robot_error instance
service instance

That's things i want the end result to appear. I have tryed to first obtain the result using this SQL

SELECT * FROM robot_error WHERE robot = '16'

making a while to loop all leads to robot_error table however i cant work out how i'm able to result in the derive from service tables to become proven, can anybody help? :)

I'd recommend using joins.

  • JOIN: Return rows when there's a minumum of one match both in tables
  • LEFT JOIN: Return all rows in the left table, even when you will find no matches within the right table
  • RIGHT JOIN: Return all rows in the right table, even when you will find no matches within the left table
  • FULL JOIN: Return rows when there's a match within the tables

But you'd need to have a relation between robot_error and repair before use a query having a join. It might most likely look something similar to this:

SELECT * FROM robot_error LEFT OUTER JOIN services ON robot_error.ID = services.FK WHERE robot = '16'

Jeff Atwood demonstrates the different Joins quite nicley