Query return result but not as expected-CodePudding

I have one query which return correct results for the parameters that are there but not what the goal is.

There are two columns month and year. What I want is when I choose from dropdowns:

4/2020 to 12/2022 to return everything after month 4 of 2020 till month 12 of 2022. Currently, when I choose 4/2020 to 12/2022 it returns everything but removes months up to 4th from each year.

Example when the query is 1/2020 to 12/2022:

country_id    rate    month    year    value 
160           1.60    3        2020    1.4
160           1.30    9        2020    1.4
160           1.2983  3        2021    1.4
160           NULL    3        2022    2
160           NULL    6        2022    1.4

When I run the query 4/2020 to 12/2022 the result is

country_id    rate    month    year    value 
160           1.30    9        2020    1.4
160           NULL    6        2022    1.4

but it must be

country_id    rate    month    year    value 
160           1.30    9        2020    1.4
160           1.2983  3        2021    1.4
160           NULL    3        2022    2
160           NULL    6        2022    1.4

Here is the query which shows all the results

SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value 
FROM `data_prod` d 
INNER JOIN monthly_data r ON r.country_id = d.country_id AND r.year=d.year AND r.month=d.month 
WHERE d.country_id IN (160) 
AND d.year BETWEEN 2020 AND 2022 
AND r.year BETWEEN 2020 AND 2022 
AND d.month BETWEEN 1 AND 12 
ORDER BY d.year, d.month

The query which removes up to 4th month (in this case) from each year

SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value 
FROM `data_prod` d 
INNER JOIN monthly_data r ON r.country_id = d.country_id AND r.year=d.year AND r.month=d.month 
WHERE d.country_id IN (160) 
AND d.year BETWEEN 2020 AND 2022 
AND r.year BETWEEN 2020 AND 2022 
AND d.month BETWEEN 4 AND 12 
ORDER BY d.year, d.month

Here is the php part

$countryIds = $countries;
$month_clause = "AND d.month BETWEEN $selectedStartMonth AND $selectedEndMonth";
        
$query = "SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value
      FROM `data_prod` d
      INNER JOIN monthly_data r ON r.country_id = d.country_id AND r.year=d.year AND r.month=d.month 
      WHERE d.country_id IN (".implode(",",$countryIds).") 
      AND d.year BETWEEN $periodStart AND $periodEnd
      AND r.year BETWEEN $periodStart AND $periodEnd
      $month_clause
      ORDER BY d.year, d.month";

Can anyone help a bit here?

CodePudding user response：

Rewrite the SQL query to:

SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value 
FROM `data_prod` d 
INNER JOIN monthly_data r ON r.country_id = d.country_id 
                         AND r.year=d.year AND r.month=d.month 
WHERE d.country_id IN (160) 
AND ((d.year=2020 and d.month>=4) OR
     (d.year>2020 and d.year<2022) OR
     (d.year=2022 and d.month<=12))
ORDER BY d.year, d.month

Or you could use this answer, and write:

SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value 
FROM `data_prod` d 
INNER JOIN monthly_data r ON r.country_id = d.country_id 
                         AND r.year=d.year AND r.month=d.month 
WHERE d.country_id IN (160) 
and date(concat_ws('-',d.year,d.month,1)) between '2020-04-01' and '2022-12-01'
ORDER BY d.year, d.month

CodePudding user response：

I would store the month and year as a datetime data type in your table and then give then a value as the first day of the desired month. That would make your code a bit slicker and you would have a smaller table and less data to search etc. Also it would be easier on your query.