BenthamLtd
asked on
Unknown column in field list
Yet again, this is probably something easy that I've completely overlooked so here it is.
In a nutshell, this is an extract of a search query on my system. Works brilliantly, apart from one of the joins/selects. Every time I run it, always returns "Unknown column 'dd_gar_how_returned.GARHo
Here is the query extract in full:
Here is explain on the ref tables for 'dd_gar_how_returned' and 'how_returned':
Any ideas, peeps?
Many thanks as always
In a nutshell, this is an extract of a search query on my system. Works brilliantly, apart from one of the joins/selects. Every time I run it, always returns "Unknown column 'dd_gar_how_returned.GARHo
Here is the query extract in full:
SELECT
sheets.Sheet_No AS Sheet_No,
sheets.Sales_Ledger_No AS Sales_Ledger_No,
sheets.CreationDateTime AS CreationDateTime,
sheets.LatestModification AS LatestModification,
sheets.LoggedBy AS LoggedBy,
sheets.CompanyName AS CompanyName,
sheets.ContactName AS ContactName,
sheets.Postcode AS Postcode,
sheets.Email AS Email,
sheets.Phone AS Phone,
sheets.InvoiceDate AS InvoiceDate,
goods_actually_returned.GoodsActuallyReturned AS GoodsActuallyReturned,
goods_actually_returned.GoodsActuallyReturned_qty AS GoodsToBeSentOnReceipt_qty,
dd_gar_quality.GARQuality AS GoodsActuallyReturned_quality,
dd_gar_type.GARType AS GoodsActuallyReturned_type,
dd_gar_supplier.GARSupplier AS GoodsActuallyReturned_supplier,
dd_gar_how_returned.GARHowReturned AS HowReturned,
consignment_no.ConsignmentNo AS ConsignmentNo
FROM sheets
LEFT JOIN goods_actually_returned ON sheets.Sheet_No = goods_actually_returned.Sheet_No
LEFT JOIN dd_gar_quality ON goods_actually_returned.GoodsActuallyReturned_quality = dd_gar_quality.GARQualityID
LEFT JOIN dd_gar_type ON goods_actually_returned.GoodsActuallyReturned_type = dd_gar_type.GARTypeID
LEFT JOIN dd_gar_supplier ON goods_actually_returned.GoodsActuallyReturned_supplier = dd_gar_supplier.GARSupplierID
LEFT JOIN dd_gar_how_returned ON dd_gar_how_returned.GARHowReturnedID = how_returned.HowReturned
LEFT JOIN consignment_no ON sheets.Sheet_No = consignment_no.Sheet_No
WHERE
sheets.Sheet_No = goods_actually_returned.Sheet_No
AND dd_gar_how_returned.GARHowReturned LIKE 'Post (Royal Mail)';
Here is explain on the ref tables for 'dd_gar_how_returned' and 'how_returned':
/* how_returned */
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| Sheet_No | int(11) | NO | | NULL | |
| HowReturned | int(11) | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+
Example data -
Sheet_No: 11123, HowReturned: 1
Sheet_No 11124, HowReturned: 2
/* dd_gar_how_returned */
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| GARHowReturnedID | int(11) | NO | PRI | NULL | auto_increment |
| GARHowReturned | varchar(50) | YES | | NULL | |
+------------------+-------------+------+-----+---------+----------------+
Example data -
GARHowReturnedID: 1, GARHowReturned: Post (Royal Mail)
GARHowReturnedID: 2, GARHowReturned: Courier
Any ideas, peeps?
Many thanks as always
Last Comment
You use an alias:
dd_gar_how_returned.GARHow
but then in the query you use the original column name:
LEFT JOIN dd_gar_how_returned ON dd_gar_how_returned.GARHow
Once you assign an alias, you have to stick with it.
dd_gar_how_returned.GARHow
but then in the query you use the original column name:
LEFT JOIN dd_gar_how_returned ON dd_gar_how_returned.GARHow
Once you assign an alias, you have to stick with it.
ASKER
Ok then, how would you write the query with the joins because everything I'm doing is throwing up either alias problems or unknown columns in on clause errors.
Cheers.
Cheers.
Try this:
Or this:
SELECT
sheets.Sheet_No AS Sheet_No,
sheets.Sales_Ledger_No AS Sales_Ledger_No,
sheets.CreationDateTime AS CreationDateTime,
sheets.LatestModification AS LatestModification,
sheets.LoggedBy AS LoggedBy,
sheets.CompanyName AS CompanyName,
sheets.ContactName AS ContactName,
sheets.Postcode AS Postcode,
sheets.Email AS Email,
sheets.Phone AS Phone,
sheets.InvoiceDate AS InvoiceDate,
goods_actually_returned.GoodsActuallyReturned AS GoodsActuallyReturned,
goods_actually_returned.GoodsActuallyReturned_qty AS GoodsToBeSentOnReceipt_qty,
dd_gar_quality.GARQuality AS GoodsActuallyReturned_quality,
dd_gar_type.GARType AS GoodsActuallyReturned_type,
dd_gar_supplier.GARSupplier AS GoodsActuallyReturned_supplier,
dd_gar_how_returned.GARHowReturned AS GHowReturned,
consignment_no.ConsignmentNo AS ConsignmentNo
FROM sheets
LEFT JOIN goods_actually_returned ON sheets.Sheet_No = goods_actually_returned.Sheet_No
LEFT JOIN dd_gar_quality ON goods_actually_returned.GoodsActuallyReturned_quality = dd_gar_quality.GARQualityID
LEFT JOIN dd_gar_type ON goods_actually_returned.GoodsActuallyReturned_type = dd_gar_type.GARTypeID
LEFT JOIN dd_gar_supplier ON goods_actually_returned.GoodsActuallyReturned_supplier = dd_gar_supplier.GARSupplierID
LEFT JOIN dd_gar_how_returned ON dd_gar_how_returned.GARHowReturnedID = how_returned.HowReturned
LEFT JOIN consignment_no ON sheets.Sheet_No = consignment_no.Sheet_No
WHERE
sheets.Sheet_No = goods_actually_returned.Sheet_No
AND GHowReturned LIKE 'Post (Royal Mail)';
Or this:
SELECT
sheets.Sheet_No AS Sheet_No,
sheets.Sales_Ledger_No AS Sales_Ledger_No,
sheets.CreationDateTime AS CreationDateTime,
sheets.LatestModification AS LatestModification,
sheets.LoggedBy AS LoggedBy,
sheets.CompanyName AS CompanyName,
sheets.ContactName AS ContactName,
sheets.Postcode AS Postcode,
sheets.Email AS Email,
sheets.Phone AS Phone,
sheets.InvoiceDate AS InvoiceDate,
goods_actually_returned.GoodsActuallyReturned AS GoodsActuallyReturned,
goods_actually_returned.GoodsActuallyReturned_qty AS GoodsToBeSentOnReceipt_qty,
dd_gar_quality.GARQuality AS GoodsActuallyReturned_quality,
dd_gar_type.GARType AS GoodsActuallyReturned_type,
dd_gar_supplier.GARSupplier AS GoodsActuallyReturned_supplier,
dd_gar_how_returned.GARHowReturned AS HowReturned,
consignment_no.ConsignmentNo AS ConsignmentNo
FROM sheets
LEFT JOIN goods_actually_returned ON sheets.Sheet_No = goods_actually_returned.Sheet_No
LEFT JOIN dd_gar_quality ON goods_actually_returned.GoodsActuallyReturned_quality = dd_gar_quality.GARQualityID
LEFT JOIN dd_gar_type ON goods_actually_returned.GoodsActuallyReturned_type = dd_gar_type.GARTypeID
LEFT JOIN dd_gar_supplier ON goods_actually_returned.GoodsActuallyReturned_supplier = dd_gar_supplier.GARSupplierID
LEFT JOIN dd_gar_how_returned ON dd_gar_how_returned.GARHowReturnedID = how_returned.HowReturned
LEFT JOIN consignment_no ON sheets.Sheet_No = consignment_no.Sheet_No
WHERE
sheets.Sheet_No = goods_actually_returned.Sheet_No
AND dd_gar_how_returned.GARHowReturned LIKE 'Post (Royal Mail)';
ASKER
Both return "Unknown column 'how_returned.HowReturned'
ASKER
Now this is weird as well, if I physically Select how_returned.HowReturned in the field list so that it looks like this:
It comes back with Unknown column 'how_returned.HowReturned'
Eh?!
SELECT
.....
how_returned.HowReturned AS HowReturnedID
LEFT JOIN dd_gar_how_returned ON dd_gar_how_returned.GARHowReturnedID = HowReturnedID
WHERE sheets.Sheet_No = goods_actually_returned.Sheet_No AND
dd_gar_how_returned.GARHowReturned = 'Post (Royal Mail)';
It comes back with Unknown column 'how_returned.HowReturned'
Eh?!
ASKER
Would it help if I listed all the "external" reference tables in the FROM statement?
E.g.
......?
E.g.
FROM sheets, dd_gar_how_returned
......?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Solved myself.
ASKER
Solved myself.
PHP
PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.
125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
dd_gar_how_returned.GARHow
If that works, replace TestHowReturned with the alias of your choice, ensuring that the alias you choose is not present as a field name in any of your tables.