I can't seem to get LEFT JOINS for this one query. Here's what the tables look like (simplified):

STUDY StudyID SponsorOrgID

STUDY_SITE StudyID SiteOrgID

LIBRARY_STUDY StudyID

Here is what I want to get. I want all the studies that are sponsored by organization 5 or assigned to study site organization 5 but are not in the library already.

select *
from   STUDY S
       left join STUDY_SITE SS on (SS.StudyID = S.StudyID)
       left join LIBRARY_STUDY LS on (LS.StudyID = S.StudyID)
where  (S.SponsorOrgID = 5 or SS.SiteOrgID = 5) and LS.StudyID is null

Here is the code:

//Get study collection and set relations to use
StudyCollection studies = new StudyCollection();
RelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(LibraryStudy.Relations.StudyEntityUsingStudyID,JoinHint.Left);
relationsToUse.Add(StudySiteEntity.Relations.StudyEntityUsingStudyID,JoinHint.Left);
relationsToUse.ObeyWeakRelations = true;

//Study is not already in library (LibraryStudy.StudyID is null)
IPredicateExpression filter = new PredicateExpression(PredicateFactory.CompareNull(LibraryStudyFieldIndex.StudyID));

//Make new subfilter for OR expression
//Study is owned by organization
IPredicateExpression subFilter = new PredicateExpression(PredicateFactory.CompareValue(StudyFieldIndex.SponsorOrgID, ComparisonOperator.Equal, thisPage.CurrentUser.OrgID));
//Study is published to organization
subFilter.AddWithOr(PredicateFactory.CompareValue(StudySiteFieldIndex.SiteOrgID, ComparisonOperator.Equal, thisPage.CurrentUser.OrgID));

//Add subfilter to filter
filter.AddWithAnd(subFilter);

//Get collection
studies.GetMulti(filter,0,null,relationsToUse);

No matter what I do with the ObeyWeakRelations or the JoinHints I get RIGHT JOINS. Here is part of the generated SQL from this example:

FROM (( [dbo].[Study] 
RIGHT JOIN [dbo].[LibraryStudy] ON  [dbo].[Study].[StudyID]=[dbo].[LibraryStudy].[StudyID]) 
RIGHT JOIN [dbo].[StudySite] ON  [dbo].[Study].[StudyID]=[dbo].[StudySite].[StudyID]) WHERE ( [dbo].[LibraryStudy].[StudyID] IS NULL And ( [dbo].[Study].[SponsorOrgID] = @SponsorOrgID1 Or [dbo].[StudySite].[SiteOrgID] = @SiteOrgID2))', N'@SponsorOrgID1 int,@SiteOrgID2 int', @SponsorOrgID1 = 5, @SiteOrgID2 = 5

I'm stumped. Any thoughts?

-Bill

relationsToUse.Add(LibraryStudy.Relations.StudyEntityUsingStudyID,JoinHint.Left);

will result in: LibraryStudy LEFT JOIN Study (so a join towards LibraryStudy)

relationsToUse.Add(StudySiteEntity.Relations.StudyEntityUsingStudyID,JoinHint.Left);

Will result in StudySite LEFT JOIN Study (so a join towards StudySite)

As Study is already in the pack, this will effectively be the same as:

LibraryStudy LEFT JOIN Study RIGHT JOIN StudySite

which is the same as Study RIGHT JOIN LibraryStudy RIGHT JOIN StudySite

What you want: Study LEFT JOIN StudySite LEFT JOIN LibraryStudy

means that you want to join away from StudySite and away from LibaryStudy.

This means that you have to specify the relations as: relationsToUse.Add(LibraryStudy.Relations.StudyEntityUsingStudyID,JoinHint.Right); relationsToUse.Add(StudySiteEntity.Relations.StudyEntityUsingStudyID,JoinHint.Right);

OR: relationsToUse.Add(Study.Relations.LibraryStudyEntityUsingStudyID, JoinHint.Left); relationsToUse.Add(StudyEntity.Relations.StudySiteEntityUsingStudyID,JoinHint.Left);

If you specify join hints, leave ObeyWeakRelations alone, it has no meaning in that context, so set it to false or don't set it at all.

As usual your support is superb. Thanks for the lesson in SQL joins. Now that I fully understand what is going on I was able to put the code together properly and it works fine. Thanks again for all your effort.

Glad it's solved!