What is the best way to sort a query based on defined priorities in another table.
I have my main 'Schedule' table here...
And I want to sort the WorkCenterID column by the values in this table, 'Operations'.
I am playing around a little with Joins, but I'm not even sure I'm on the right track.
SELECT Schedule.JobID, Schedule.WorkCenterID, Schedule.ProcessID, Operations.Operation, Operations.OpOrder, Schedule.EstimatedProductionHours, Operations.OpOrder
FROM Schedule INNER JOIN
Operations ON Schedule.ProcessID = Operations.Operation
ORDER BY Operations.OpOrder