How to fix repeating data from SQL join?

user_16109871 0
Viewed 33

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 joined them with this query:

SELECT table1.PERSON_ID,
       table1.GRADE_LEVEL,
       table1.START_OF_SCHOOL,
       table1.END_OF_SCHOOL,
       table2.EFFECTIVE_DATE
FROM table1
JOIN table2 ON table1.PERSON_ID = table2.PERSON_ID

This is the result

| PERSON_ID | GRADE_LEVEL  | START_OF_SCHOOL| END_OF_SCHOOL | EFFECTIVE_DATE |
|-----------|--------------|----------------|---------------|----------------|
| 1         | 9            | 08/23/2010     | 05/28/2011    | 10/28/2010     |
| 1         | 10           | 08/22/2011     | 05/26/2012    | 10/28/2010     |
| 1         | 11           | 08/27/2012     | 06/01/2013    | 10/28/2010     |
| 1         | 12           | 08/26/2013     | 05/31/2014    | 10/28/2010     |
| 1         | 9            | 08/23/2010     | 05/28/2011    | 10/28/2011     |
| 1         | 10           | 08/22/2011     | 05/26/2012    | 10/28/2011     |
| 1         | 11           | 08/27/2012     | 06/01/2013    | 10/28/2011     |
| 1         | 12           | 08/26/2013     | 05/31/2014    | 10/28/2011     |
| 1         | 9            | 08/23/2010     | 05/28/2011    | 10/28/2012     |
| 1         | 10           | 08/22/2011     | 05/26/2012    | 10/28/2012     |
| 1         | 11           | 08/27/2012     | 06/01/2013    | 10/28/2012     |
| 1         | 12           | 08/26/2013     | 05/31/2014    | 10/28/2012     |
| 1         | 9            | 08/23/2010     | 05/28/2011    | 10/28/2013     |
| 1         | 10           | 08/22/2011     | 05/26/2012    | 10/28/2013     |
| 1         | 11           | 08/27/2012     | 06/01/2013    | 10/28/2013     |
| 1         | 12           | 08/26/2013     | 05/31/2014    | 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. How do I get the table to look like this? (EFFECTIVE_DATE in the right date range and correct GRADE_LEVEL tied to it)

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

Just add that condition to your join clause:

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

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
user_11440350
Related