Concatenate two columns from one table and look for where it does not show up in another table mysql-CodePudding

I want to concatenate two columns from table1 and check if they are not in table2. table1 has column fname and name, I want to concatenate all fname and lname and check look for the ones that are not in table2 name column. I am able to check if fname alone does not show in another table2, I am also able to concatenate fname and lname as qname columns in table 1, but when I concatenate as qname and check if qname is not in table two then I get an error Error in query (1054): Unknown column 'qname' in 'IN/ALL/ANY subquery'

Here is my query

SELECT CONCAT(table1.fname, " ", table1.lname) AS qname from table1 
WHERE qname NOT IN
    (SELECT table2.name 
     FROM table2);

CodePudding user response：

You need to use the concat function in the where clause, as the field qname is not present at that stage of the query processing.

SELECT CONCAT(table1.fname, " ", table1.lname) AS qname from table1 
WHERE CONCAT(table1.fname, " ", table1.lname) NOT IN
    (SELECT table2.name 
     FROM table2);

You could also use a left-join query to resolve the same issue.

SELECT CONCAT(table1.fname, " ", table1.lname) AS qname 
FROM table1 
LEFT JOIN table2
  ON CONCAT(table1.fname, " ", table1.lname) = table2.name
WHERE table2.name IS NULL;