I have a bit of a difficult problem that has been bugging me for two weeks. I need to join two tables together, but if there is no matching right-hand record it needs to select a "default record".
It's for a work registration package where I need to join on a "work factor" table. I have about 200 kinds of work, and about 10 of them have a factor. The rest should link to a default factor. The table with the factors holds a foreign key to the table with the actual work.
-- Work -- -- Factor --
Name WorkID WorkID Factor
Type 1 1 0 1 // DEFAULT
Type 2 2 1 1.5
Type 3 3 2 1.2
Type 4 4
Type 1 1.5
Type 2 1.2
Type 3 1.1
Type 4 1 // DEFAULT
So, if there is no matching factor for the work, it needs to join on a "default" factor.
This expands to a table that contains the actual work done on the workfloor, but if I can get this going that won't be much of a problem.
I hope someone can help me with this.
 I should mention that I'm using the JET engine [/edit]
SELECT Work.work_name, factor.factor
FROM work LEFT JOIN factor ON factor.work_id = work.work_id OR factor.work_id = 0
WHERE factor.work_id = (SELECT MAX(factor.work_ID) FROM factor WHERE factor.work_id = work.work_id OR factor.work_id = 0);
But with the subquery and all I have the feeling this is not particularly one of the most efficient methods.