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.
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