Find the Min and Max date from two tables from a sql select statement-CodePudding

Cant seem to wrap my head round this problem.

I have two tables one which has the following sample values:

Second table had the following values:

What i am trying to achieve is like the following:

So you can see the first table has the modules, what year and what term. Based on these there is a start week and and end week.

The lookup table for the start and the finish unfortunatley is in a week basis and i need the begin week to match the second tables weekNo based on the season i guess and taking the start date being Sdate from that table to match what i am looking for and then the same applies to the end date. Match the season and the endweek with the second tables WeekNo and Edate and only bring that date in.

Hope i made a bit of sense but i am hoping the third image shows what i am look for.

I've tried CTE, Group by, Partition by, order by, min, max and got nowhere :( Dont really want to hard code anything, so was hoping you wonderful peps can help me out !!

Many thanks in advance :)

CodePudding user response：

I suspect you are trying to achieve this by using one a single join between the tables - whereas what you actually need is two separate joins:

SELECT table1.module as mod_code, 
       table1.season as psl_code, 
       table2.Sdate as ypd_sdate,
       table3.Edate as ypd_edate
FROM t1 as table1
     JOIN t2 as table2 ON table2.yr = table1.year AND table2.season = table1.season AND table2.weekNo = table1.BeginWeek
     JOIN t2 as table3 ON table3.yr = table1.year AND table3.season = table1.season AND table3.weekNo = table1.EndWeek