WHERE ...IN ()  using concat()

Posted on 2005-04-09
Last Modified: 2008-03-17
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'

Question by:drew22
    LVL 10

    Expert Comment

    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.
    LVL 10

    Accepted Solution

    Use like this:

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

        SELECT  concat (b.lastname, AS namezip
        FROM survey.participant b

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now