Question 20324 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I return when a column changes values from one month to the next in SQL? – Mysql

I have three different tables in SQL that represent months (jan, feb, mar). Each table has the same columns (ID, DATE, CSNA). An example for jan would be:

    ID        DATE         CSNA
1234770286  2/2/2023    Blue 10 A V 
1234770301  2/2/2023    Blue 10 C IV
1234770316  2/2/2023    Blue 10 B V 
1234770331  2/2/2023    Blue 5 A III

and an example for feb would be:

1234770286  2/2/2023    Red 10 A V 
1234770301  2/2/2023    Blue 10 C IV
1234770316  2/2/2023    Blue 10 B V 
1234770331  2/2/2023    Red 5 A III

The CSNA value sometimes changes from month to month, or it can stay the same. Also, some new rows may be added each month. I specifically want to find the rows and ID’s where the CSNA value was changed from jan-feb and feb-mar. In the example, I’d want to return the first and last ID’s (1234770286, 1234770331) since the CSNA data is different.

I tried running this query in mysql workbench to see changes from jan-feb:

SELECT *
FROM feb_data AS f, jan_data AS j
HAVING j.CSNA<>f.CSNA OR j.CSNA IS NULL

it seems to return more rows than are in either table and that is confusing me. I basically only want to return rows when the CSNA value is different from table 1 (jan) and table 2 (feb), and not bother showing rows when they are the same. Any help would be greatly appreciated.

Tags:

2

Answers


  1. 0

    the question is not entirely clear how these tables are related to each other, but I think it will help you solve the problem, or at least get closer to a solution.

    select 
  * 
from 
  feb_data AS ff 
where 
  ff.id not in (
    SELECT 
      f.id 
    FROM 
      feb_data AS f, 
      jan_data AS j 
    where 
      j.CSNA = f.CSNA 
      and j.DATE = f.DATE 
      and j.CSNA IS NOT NULL
  ) 
  or ff.CSNA is null
    Login or Signup to reply.
  2. 0

    Seems we can join our tables by id, then we can do it using INNER JOIN as follows :

    select j.ID, j.DATE as jan_date, f.DATE as feb_date, j.CSNA as jan_CSNA, f.CSNA as feb_CSNA
from jan_data j
inner join feb_data f on f.CSNA <> j.CSNA AND f.id = j.id

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search