in reply to mapping coordinates- suggestion needed
I haven't put this into DBI syntax I just drummed it up quickly in Access but the SELECT .. FROM .. syntax should copy over. My results on your test data are.SELECT Jobs.Job, Users.User, Count(Users.User) AS CountOfUser FROM Jobs LEFT JOIN Users ON ( ( Jobs.Start <= Users.Start and Jobs.Stop >= Users.Start ) or ( Jobs.Start <= Users.Stop and Jobs.Stop >= Users.Stop ) or ( Users.Start <= Jobs.Start and Users.Stop >= Jobs.Start ) or ( Users.Start <= Jobs.Stop and Users.Stop >= Jobs.Stop ) ) GROUP BY Jobs.Job, Users.User HAVING Count(Users.User) = 1;
Job User CountOfUser job_id1 uname1 1 job_id2 uname2 1 job_id3 uname2 1 job_id4 uname2 1
|
|---|