Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1085
  • Last Modified:

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
0
drew22
Asked:
drew22
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now