MySQL not exists unknown column error

Question

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

i think you are asking the wrong sql to the database i think you need something more like GROUP BY person_id HAVING SUM(state = 'active') = 1 AND SUM(state = 'passive') = 0... But iam not sure and cant test it without example data and expected output — Raymond Nijland
– Raymond Nijland, Commented May 4, 2017 at 12:41

Darshan Mehta · Accepted Answer · 2017-05-04 12:42:16Z

1

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
    )

edited May 4, 2017 at 12:42

answered May 4, 2017 at 12:36

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bernd Buffen Over a year ago

@Ali insan - do not use * in the select. you can use SELECT 1 that is much faster

Gordon Linoff · Accepted Answer · 2017-05-04 12:46:49Z

1

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.

answered May 4, 2017 at 12:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Jim Macaulay · Accepted Answer · 2017-05-04 13:13:47Z

1

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

edited May 4, 2017 at 13:13

answered May 4, 2017 at 12:40

Jim Macaulay

5,2515 gold badges32 silver badges60 bronze badges

Collectives™ on Stack Overflow

MySQL not exists unknown column error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related