How to left join with multiple "on" conditions

rdm

I am attempting to set up a left join where there are two "on" equalities.

In T-SQL, I could write this:

SELECT w.WeekDescription
FROM dbo.FridayPlanning p
LEFT JOIN dbo.WeekList w
    ON p.QuarterNumber = w.[Quarter]
        AND p.WeekNumber = w.[Week]

I have been looking through the examples on the Joins page, but I am not sure how to indicate multiple on statements in .LeftJoin("table","field1","=","field2")

Is this type of join possible?

rdm

I looked in the source code (i.e., Editor.cs) in hopes I could find a function I could extend, but I had no such luck.

In Linq, I could do multiple "on" conditions like in the example below. I was hoping I could do something along these lines.

var joinQuery =
   from t1 in Table1
   join t2 in Table2
      on new { t1.Column1, t1.Column2 } equals new { t2.Column1, t2.Column2 }

allan

This thread contains a hack to allow this to work in PHP. I haven't tried it on .NET, but I don't see any reason why it wouldn't work.

Allan

rdm

@Allan -- Thanks. I was able to translate that hack into C# and verified that it works in my scenario. I've translated that answer into a set of formulas using generic names. Could we place this in documentation?

// T-SQL  
SELECT *
FROM dbo.Table1 t1
LEFT JOIN dbo.Table2 t2
    ON t1.Column1 = t2.Column1
    AND t1.Column2 = t2.Column2
 
// C# -- Note: Do not use hard returns in strings
.LeftJoin("Table2", "Table2.Column1", "=", "Table1.Column1 and (Table2.Column2 = Table1.Column2)")
 
// PHP -- Note: Do not use hard returns in strings
 ->leftJoin('Table2','Table2.Column1', '=', 'Table1.Column1 AND (Table2.Column2 = Table1.Column2))')

allan

Could we place this in documentation?

No - not yet. I'm not certain that I want to maintain that API or offer a different more formal way of doing it. As I mentioned this is a hack for the moment. I expect it to continue working, but it might not if I do formalise the API for it.

Allan