Unknown Column X in field list

Question

I have an "unknown column in field list" error in my sql query that I cannot figure out. I read there could be hidden characters so I manually typed and ensured the column names are correct. I tried different queries which worked fine. In this case Query 1 can query the table and columns but Query 2 errors out with Unknown column 'rec.ID' in field list

Query 1:

SELECT rec.`ID` FROM rec; // works fine

Query 2:

// error 
SELECT 
    rec.`ID`,
    rec.`Status Type`, 
    lender.`ID`,
    lender.`Cost Per`
FROM 
    ( SELECT rec.`ID`, rec.`Status Type` FROM rec) AS T1
JOIN (SELECT lender.`ID`, lender.`Cost Per` FROM lender) AS T2
ON T1.`ID` = T2.`ID`
WHERE rec.`Lead Type` = "Activated"

you do not specify what db you are using, but this is a more mature question with a ton of helpful answers https://stackoverflow.com/questions/1346209/unknown-column-in-field-list-error-on-mysql-update-query — frederj, Feb 02 '22 at 19:45

score 2 · Accepted Answer · answered Jul 31 '21 at 00:44

2

Try the following:

SELECT 
    T1.`ID`,
    T1.`Status Type`, 
    T2.`ID` as ID2, -- to keep column names different
    T2.`Cost Per`
FROM 
    ( SELECT rec.`ID`, rec.`Status Type` FROM rec) AS T1
JOIN (SELECT lender.`ID`, lender.`Cost Per` FROM lender) AS T2
ON T1.`ID` = T2.`ID`
WHERE T1.`Lead Type` = "Activated"

answered Jul 31 '21 at 00:44

coffeinjunky

11,254
39
57

1

Thank you for the example. I now realize the outter SELECT must refer to the alias assigned to the table in the subqeury. This works perfectly. Thank you. – Potion Jul 31 '21 at 01:00

score 1 · Answer 2 · answered Jul 31 '21 at 01:11

Don't use subqueries:

SELECT r.`ID`, r.`Status Type`, l.`ID`, l.`Cost Per`
FROM rec r JOIN
     lender l
     ON r.`ID` = l.`ID`
WHERE r.`Lead Type` = 'Activated';

Notes:

Although it doesn't shouldn't a difference, some databases materialize subqueries which incurs additional overhead.
Note the use of table aliases so the query is easier to write and to read.
Use single quotes for string and date constants.
There is no need to select l.ID. It is the same as r.ID.

Unknown Column X in field list

2 Answers2