MySQL Unknown Column, but column exists

Question

Running the following MySQL Query and am getting this error:

database error Unknown column 'qd.ItemListID' in 'on clause'

SELECT 
    IFNULL(hqp.IsActive, qd.ItemName) AS Item_Name, DATE_FORMAT(IFNULL(hqp.SalesDate, qd.SalesDate), '%m-%d-%Y') AS effectDate, IFNULL(hqp.NoBid, qd.NoBid) AS noBid, IFNULL(hqp.VendorName, qd.VendorName) AS vendor, IFNULL(hqp.Source, qd.Source) AS source, IFNULL(hqp.Type, qd.Type) AS type, IFNULL(hqp.Cost, qd.PurchaseCost) AS cost, IFNULL(hqp.Price, qd.SalesPrice) AS price, IFNULL(hqp.ConditionCode, '') AS conditionCode, qi.UnitOfMeasureSetRef_FullName AS uom
FROM wp_quantum_data AS qd, wp_hunter_quote_parts AS hqp
LEFT JOIN wp_quickbook_items AS qi ON (qi.ListID = IFNULL(qd.ItemListID, hqp.Item_ListID))
WHERE qd.IsActive = 1 || hqp.IsActive = 1
GROUP BY Item_Name
ORDER BY Item_Name ASC

The column exists in the wp_quantum_data table so I can't explain why this error is occurring. I've tried renaming the column in phpmyadmin by typing the column name in and saving the column structure, but it is still saying that the column doesn't exist.

Did you check if your case is correct? ItemListID, ItemListId, ... — Felippe Duarte, Feb 10 '17 at 20:06
Yes, the column name is set to `ItemListID`, and this is correct in the `wp_quantum_data` table, which I recently renamed from `wp_quickbook_vendor_items`. could it have something to do with changing the name of the database table? Which doesn't make sense to me either. — Solomon Closson, Feb 10 '17 at 20:07
`LEFT JOIN wp_quickbook_items AS qi`, is there AS needed? I normally use without it. — laser, Feb 10 '17 at 20:09
@JiriHrazdil `FROM wp_quantum_data AS qd, wp_hunter_quote_parts AS hqp` is it from two tables select? — laser, Feb 10 '17 at 20:11
@SolomonClosson: what happens when you execute the above query directly in phpMyAdmin/Adminer/mysql console? — Jirka Hrazdil, Feb 10 '17 at 20:14
@JiriHrazdil Yes, I need to grab from 2 tables, but need to prioritize the `wp_hunter_quote_parts` table over the `wp_quantum_data` table, which I'm doing inside of the selects. Basically, if it's not found inside of the `wp_hunter_quote_parts` table, than I need to get it from the `wp_quantum_data` table (if found in there), so I need to select both tables for this, in order to be sure we get data to work with. — Solomon Closson, Feb 10 '17 at 20:14
I get the same result in phpmyadmin: `Unknown column 'qd.ItemListID' in 'on clause'` — Solomon Closson, Feb 10 '17 at 20:15
@SolomonClosson but if your select takes a row from table `wp_hunter_quote_parts`, then `qd` is not defined for that row at all? and ItemListID is non existent? I cannot understand your query logic. — laser, Feb 10 '17 at 20:16
@SolomonClosson to avoid such complexities, I would prefer LEFT JOIN method — laser, Feb 10 '17 at 20:17
@SolomonClosson: does `ItemListID` column exist in table `wp_hunter_quote_parts` or `wp_quickbook_items`? — Jirka Hrazdil, Feb 10 '17 at 20:18
Problem with a Join method here is that if the FROM table is empty it won't return anything on the other table. I need it to return from the other table, even if FROM table is an empty result. — Solomon Closson, Feb 10 '17 at 20:18
`ItemListID` exists in `wp_quantum_data`, `Item_ListID` exists in `wp_hunter_quote_parts`, and `ListID` exists in `wp_quickbook_items` — Solomon Closson, Feb 10 '17 at 20:21
@SolomonClosson can you use just JOIN? http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables — laser, Feb 10 '17 at 20:22
@SolomonClosson maybe it is best for you right now is to run EXPLAIN on your select. — laser, Feb 10 '17 at 20:27
Can you show me one example using ON in this way: `ON (qi.ListID = IFNULL(qd.ItemListID, hqp.Item_ListID))` instead of `ON (qi.ListID = qd.ItemListID OR qi.ListID = hqp.Item_ListID)` — McNets, Feb 10 '17 at 20:28

Barmar · Accepted Answer · 2017-02-10T20:34:11.093

1

The problem is that you're mixing the archaic implicit JOIN syntax with LEFT JOIN. The LEFT JOIN only combines with the table immediately before it, which is wp_hunter_quote_parts; you can't refer to columns in wp_quantum_data in the ON clause.

You should get out of the habit of using implicit joins, and use explicit JOIN clauses for everything.

You also seem to have your joins in the wrong order. Since the row can be missing in wp_hunter_quote_parts, that's the table you should LEFT JOIN with.

SELECT 
    IFNULL(hqp.IsActive, qd.ItemName) AS Item_Name, DATE_FORMAT(IFNULL(hqp.SalesDate, qd.SalesDate), '%m-%d-%Y') AS effectDate, IFNULL(hqp.NoBid, qd.NoBid) AS noBid, IFNULL(hqp.VendorName, qd.VendorName) AS vendor, IFNULL(hqp.Source, qd.Source) AS source, IFNULL(hqp.Type, qd.Type) AS type, IFNULL(hqp.Cost, qd.PurchaseCost) AS cost, IFNULL(hqp.Price, qd.SalesPrice) AS price, IFNULL(hqp.ConditionCode, '') AS conditionCode, qi.UnitOfMeasureSetRef_FullName AS uom
FROM wp_quantum_data AS qd
LEFT JOIN wp_quickbook_items AS qi ON qi.ListID = qd.ItemListID
LEFT JOIN wp_hunter_quote_parts AS hqp ON qi.ListID = hqp.ItemListID AND hqp.IsActive = 1
WHERE qd.IsActive = 1
GROUP BY Item_Name
ORDER BY Item_Name ASC

edited Feb 10 '17 at 20:34

answered Feb 10 '17 at 20:27

Barmar

741,623
53
500
612

No, it is possible, that `wp_quickbook_items` will not have anything in it also. So I need to account for that. – Solomon Closson Feb 10 '17 at 20:31
`wp_quantum_data` will most likely have the most results in it, since this is coming from an import of data. `wp_hunter_quote_parts` is only getting populated when quotes are approved, so it will populate depending on approved status of quote. These 2 tables are very identical and need to be merged so that I grab the new data (`wp_hunter_quote_parts`) when it is available ONLY, otherwise the old data (`wp_quantum_data`). The addition of `wp_quickbook_items` is really only needed for getting the Unit of Measure (UOM) if exists. – Solomon Closson Feb 10 '17 at 20:34
OK, I changed `wp_quantum_data` to be the master table, with left joins to both other tables. – Barmar Feb 10 '17 at 20:34
Is is possible that `wp_quantum_data` table will not have the Item in it also, as I don't want to store any new Items in this table. New Items will go into the `wp_hunter_quote_parts` table. – Solomon Closson Feb 10 '17 at 20:41
So which table has the complete list of items? – Barmar Feb 10 '17 at 20:42
You wrote above "if it's not found inside of the wp_hunter_quote_parts table, than I need to get it from the wp_quantum_data table". That's why I thought `wp_quantum_data` is the primary table. – Barmar Feb 10 '17 at 20:43
Neither. `wp_hunter_quote_parts` has new items, `wp_quantum_data` has imported/old items. We need information from `wp_hunter_quote_parts` if exists, otherwise, grab from the `wp_quantum_data` if it exists in there. – Solomon Closson Feb 10 '17 at 20:43
It sounds like you need a `FULL OUTER JOIN`, but MySQL doesn't have that. See http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql for the workarounds. – Barmar Feb 10 '17 at 20:44
`wp_quantum_data` is only being used for historical purposes, but when the item gets put into the `wp_hunter_quote_parts` table, it now should override the historical data. – Solomon Closson Feb 10 '17 at 20:44

MySQL Unknown Column, but column exists

1 Answers1