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

Questions

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

November 9, 2021

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);

>Solution :

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;