Joining with more than one match?

I hope someone can help. What I'm attempting to do is probably easy, but I cannot figure it out.

I want to join on MULTIPLE conditions when I do a join (left, inner, whatever).

I cannot get the sql to look correct. In the below scenario, I would like to have multiple conditions met in order for the left outer join to come back with any data, but whenever I use a WHERE or AND clause post the LeftOuterJoin it just doesn't format the actual sql correctly. This is for MySql.

.LeftOuterJoin(Usersetting.UserIdColumn, Friendmap.FriendUserIdColumn) // this works

.LeftOuterJoin(Usersetting.UserIdColumn, Friendmap.FriendUserIdColumn).And(Usersetting.Columns.SettingId).IsEqualTo(5) //does NOT work

.LeftOuterJoin(Usersetting.UserIdColumn, Friendmap.FriendUserIdColumn).Where(Usersetting.Columns.SettingId).IsEqualTo(5) //does NOT work

Again, the latter two examples always append a where after the join has happened -- and I expect the sql to look like this:

...left outer join `usersetting` on `usersetting`.`userid` = `friendmap`.`frienduserid` and `usersetting`.`settingid` = 5

but it always ends up like this:

...left outer join `usersetting` on `usersetting`.`userid` = `friendmap`.`frienduserid`

where `usersetting`.`settingid` = 5

Any help?? I would love to hear it. Thanks so much.

I know what you are trying to do and I've used it myself in SQL Server to make joins easier. However, is there a reason you can't specify the second constraint in the "Where" clause?

Thanks for the reply. I would put all the constraints in the where clause if these were inner joins, but because I'm using a left outer, and don't always expect the joins to return any data, I don't want the constraints in the where clause because my understanding is that then the query would return no results...for example:

select `user`.`FirstName`, `setting`.`settingvalue` from `user` left outer join `setting` on `setting`.`userid` = `user`.`userid` and `setting`.`settingid` = 5 where `user`.`username` = 'myuser'

This query could return the user's first name and the settingvalue if it exists, but the setting value could also be null

When I attempt to join like this using the query tool, my sql always looks like this:

select `user`.`FirstName`, `setting`.`settingvalue` from `user` left outer join `setting` on `setting`.`userid` = `user`.`userid` where `user`.`username` = 'myuser' and `setting`.`settingid` = 5

Because the settingid constraint is in the main where clause and not part of the left outer join, this query will return no results. That's the problem I'm encountering...

I actually don't know if this is possible with Query 2.1, but could you do your left outer join against a subquery that would generate something like this?

select user.firstName, setting.settingvalue
from user
left outer join (select setting.userid, setting.settingvalue from setting where setting.settingvalue = 5) as setting on user.userid = setting.userid