0 votes
10 views
in MySQL by

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.

1 Answer

0 votes
by
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 

Related questions

0 votes
1 answer 6 views
0 votes
1 answer 4 views
0 votes
1 answer 683 views
+1 vote
1 answer 8.3k views
+1 vote
1 answer 2.2k views
0 votes
2 answers 4.5k views
asked Jul 17, 2018 in General by anonymous
...