WHERE ...IN () using concat()

Trying to find out what records from legacy.f_group  exist in survey.participant.

This works:

SELECT  con_id, lastname
FROM legacy.f_group
WHERE lastname
IN (

   SELECT lastname
    FROM survey.participant
)

...but when I try to narrow it down using 2 cols, I get an error:


SELECT  con_id, concat (lastname,zip) AS namezip
FROM legacy.f_group
WHERE namezip
IN (

    SELECT  concat (lastname,zip) AS namezip
    FROM survey.participant
)

#1054 - Unknown column 'namezip' in 'scalar IN/ALL/ANY subquery'


drew
LVL 1
drew22Asked:
Who is Participating?
 
kupra1Commented:
Use like this:

SELECT  a.con_id, concat (a.lastname,a.zip) AS namezip
FROM legacy.f_group a
WHERE concat (a.lastname,a.zip)
IN (

    SELECT  concat (b.lastname,b.zip) AS namezip
    FROM survey.participant b
)
0
 
kupra1Commented:
This is happening because Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined.
You can use an alias to refer to a column in GROUP BY, ORDER BY, or HAVING clauses.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.