7

This question has been asked several times and even answered as well but it's not helpful to me, that's why I am posting it again. I have this query:

select u.*,l.*, 
(
    select count(cr.id) 
    from cloud_recordings cr 
    left join cameras c 
        on c.owner_id=u.id 
    where c.id=cr.camera_id
) valid_licence
from users u 
left join licences l 
    on l.user_id=u.id;

It works fine, but when I put a where clause at the end:

select u.*,l.*, 
(
    select count(cr.id) 
    from cloud_recordings cr 
    left join cameras c 
        on c.owner_id=u.id 
    where c.id=cr.camera_id
) valid_licence
from users u 
left join licences l 
    on l.user_id=u.id
where valid_licence > 0;

It simply gave me an error:

ERROR: column "valid_licence" does not exist
LINE 4: left join licences l on l.user_id=u.id where valid_licence >..

0

2 Answers 2

8

You can't reference an alias on the same "level" where it was defined. You need to wrap your base query into a derived table:

select *
from (
  select u.*,
         l.*, 
         (select count(cr.id) from cloud_recordings cr left join cameras c on c.owner_id=u.id where c.id=cr.camera_id) valid_licence
  from users u 
    left join licences l on l.user_id=u.id 
) t
where valid_licence > 0;
1
  • 1
    +1. Pretty sure they'll get some other error though (due to the ORMish sillymess of having an id in every table ;) Commented Mar 14, 2016 at 12:42
1

To understand this error you need to understand that the SELECT is actually being parsed last. At the time WHERE is being parsed the SELECT statement hasn't been run and so valid_license has not been set up as an alias/computation.

Discussed more here: https://www.brentozar.com/archive/2015/07/logical-query-processing/

or google Logical Query Processing

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.