September 2

Find records from table where changed value of one of columns

For example we have table with customer loan applications, where each customer provide count of child. For simplify task let it be table with 2 columns: name and childs

+====+=======+===========+
| id | name  | childrens |
+====+=======+===========+
| 1  | John  | 1         |
+----+-------+-----------+
| 2  | Sarah | 3         |
+----+-------+-----------+
| 3  | John  | 5         |
+----+-------+-----------+

We can see John change his children count from 1 to 5. How we can figure out client such John?

It can be done at least two ways.

  1. using JOIN tables with itself
select distinct loan_applications.name
from loan_applications
join loan_applications copy on 
	loan_applications.childrens <> copy.childrens and loan_applications.name = copy.name
;
  1. using EXISTS statement
select distinct name
from loan_applications
where exists (
	select childrens from loan_applications copy where loan_applications.childrens <> copy.childrens and loan_applications.name = copy.name
);

Both queries reruns same result:

+======+
| name |
+======+
| John |
+------+

SQL online editor: https://sqlize.online/sql/mariadb/7734745c336ba185c057badb9d3163b8/