How to fix repeating data from SQL join?

Question

I have 2 tables:

table1:

| PERSON_ID | GRADE_LEVEL |START_OF_SCHOOL| END_OF_SCHOOL|
|------------|---------------|------------------|-----------------|
| 1             | 9                | 08/23/2010     | 05/28/2011    |
| 1             | 10              | 08/22/2011     | 05/26/2012    |
| 1             | 11              | 08/27/2012     | 06/01/2013    |
| 1             | 12              | 08/26/2013     | 05/31/2014    |

table2:

| PERSON_ID | EFFECTIVE_DATE|
|--------------|---------------------|
| 1              | 10/28/2010      |
| 1              | 10/28/2011      |
| 1              | 10/28/2012      |
| 1              | 10/28/2013      |

I don't want the EFFECTIVE_DATE or GRADE_LEVEL tied to each of the other rows when the date ranges don't match. I need EFFECTIVE_DATE in the right date range and correct GRADE_LEVEL tied to it.

Answer 1

Include the date comparison in the on clause:

FROM table1 JOIN
     table2
     ON table1.PERSON_ID = table2.PERSON_ID AND
        table2.EFFECTIVE_DATE BETWEEN table1.START_OF_SCHOOL AND table1.END_OF_SCHOOL