In theory a user can only have one job, but because I have a versioning system, I need to only return jobs where job.LiveVersion = 1 (so if a user has three versions of a job it should only return the single LiveVersion=1 of them and if a user is joined to a job which has records but where none of them are LiveVersion=1, the user should be returned, but without a job).
Naive SQL is SELECT user.FirstName, job.Salary FROM user INNER JOIN job ON user.JobID = job.JobID WHERE user.LastName LIKE 'b%' AND job.LiveVersion = 1.
The problem is that this then excludes any users who for whatever reason are joined to a job without a live version :-<
I'm trying to solve the generalized problem as part of an ORM, but if I could solve this I think I could generalize. Is it a subquery that is required?
Any help much appreciated! I'm running MSSQL 2000/2005 if dialect makes a difference.