Subsonic 2.2 can't work out how to do my joins

Question

Hello!

I'm quite new to subsonic and I am trying to do an almight great query with lots of joins.. I can do it in sql but really want to do it in subsonic but cant work out how..

this is the query in SQL

SELECT dbo.Job.JobId,

    dbo.Job.JobReference,
        dbo.Job.Title, 
        dbo.JobCategory.CategoryId, 
        dbo.JobOccupation.OccupationId, 

        dbo.Category.Name 

FROM dbo.Job

        LEFT OUTER JOIN
            dbo.JobType 
            ON dbo.Job.JobTypeId = dbo.JobType.TypeId 
        LEFT OUTER JOIN
            dbo.Category 
        INNER JOIN
            dbo.JobCategory 
            ON dbo.Category.CategoryId = dbo.JobCategory.CategoryId 
            ON dbo.Job.JobId = dbo.JobCategory.JobId
        LEFT OUTER JOIN
            dbo.Occupation
        INNER JOIN
            dbo.JobOccupation 
            ON dbo.Occupation.OccupationId = dbo.JobOccupation.OccupationId 
            ON dbo.Job.JobId = dbo.JobOccupation.JobId 
        LEFT OUTER JOIN
            dbo.JobSkillLevelRequired 
        INNER JOIN
        dbo.Skill 
            ON dbo.JobSkillLevelRequired.SkillId = dbo.Skill.SkillId 
            ON dbo.Job.JobId = dbo.JobSkillLevelRequired.JobId

My main problem is on the category and occupation tables.. JobCategory and JobOccupation are link tables to category and occupation, each job may have one or ther other, both or neither.. but I still need to bring back all the jobs regardless of whether they have categories and applications. I can't work out how to do this bit:

INNER JOIN dbo.JobCategory
ON dbo.Category.CategoryId = dbo.JobCategory.CategoryId
ON dbo.Job.JobId = dbo.JobCategory.JobId

Can anyone help? (i'm working with c# btw)

Thanks

bex

Answer 1

SubSonic 2 can't join on multiple columns per default. I stumbled upon this a while ago:

http://stackoverflow.com/questions/2780028/subsonic-2-join-on-multiple-columns

I always wanted to implement this but I haven't found the time to update the sources. I would suggest you use an InlineQuery instead.