Please could someone help with the general question of how to join unrelated tables?

We are using v2.0.0.0 final, Selfservice, SQL Server 2000/5.

We have a legacy database with an employee job history table EmpJobHist with these aliases:

Pk1Site Pk2JobTitle Pk3Dept Pk4JobHistory (unique) Pk5Employee Schedule Team Startdate EndDate

We need to join this using Schedule and Team to a TeamDay table where TeamDay.Pk4Date is BETWEEN EmpJobHist.StartDate and EmpJobHist.EndDate:

Pk1Schedule Pk2Calendar (irrelevent) Pk3Team Pk4Date

I can't create a TypedList joining these as there is no direct relation.

In SQL I would (kinda) "select * from EmpJobHist left join TeamDay on EmpJobHist.Schedule = TeamDay.Pk1Schedule and EmpJobHist.Team = TeamDay.Pk3Team and TeamDay.Pk4Date between (EmpJobHist.StartDate and EmpJobHist.EndDate)"

This join is at the heart of our app so it is important to make this efficient.

I maybe found the answer: add Relations in the model?

Sorry - I'm new to this.

Yup, if I create relations until I can see both EmpJobHist and TeamDay in the TypedList designer, I can make a TypedList.

The Fill loads 67580 employee days into a Janus grid in about 5 seconds.

I had to rename Schedule to ScheduleID etc on EmpJobHist, as this now has a Schedule object(s), but I understand that.

This just gets better.

I maybe found the answer: add Relations in the model?

Glad you found it.