for a couple of hours I have been working on this problem.
I have a table in MySQL called person_state
----------
person_state
----------------------------
id | person_id | state | date
----------------------------
I need to get active persons last activation dates, The state of every person is has been changed many times(States are : active, passive, waiting, blocked). If a person is activated then deactivated, my query should not get it.
My query is
select id as activation_id, person_id as active_person_id
from person_state
where state = 'active'
and
not exists(
select * from person_state
where person_id = active_person_id
and
id > activation_id
)
I am getting error Unknown column 'active_person_id' in 'where clause' .
Thanks for your your time
WHERE gets executed before SELECT and hence, you can't use column aliases inside it, try with column name instead, e.g.:
select id as activation_id, person_id as active_person_id
from person_state ps
where state = 'active'
and
not exists(
select id from person_state
where person_id = ps.person_id
and
id > ps.activation_id
)
You can use below query
select P.id as activation_id, P.person_id as active_person_id
from person_state P
where state = 'active'
and
not exists(
select 1 from person_state
where person_id = P.active_person_id
and
id > P.activation_id
);
You have missed to provide alias. You can either give 1 or * to select the date. But 1 works faster as it checks for exists and it search only for one column
Whenever you have more than one column in a table, you should use qualified column names:
select ps.id as activation_id, ps.person_id as active_person_id
from person_state ps
where ps.state = 'active' and
not exists (select *
from person_state ps2
where ps2.person_id = ps.person_id and
ps2.id > ps.id
) ;
Your main problem is that you cannot use the column aliases in the subquery.