Unknown Colum in Field List - But column DOES exist!

[the_opposite]

I've got this annoying issue that I hope somebody could help with.

 

QUERY:

SELECT DISTINCT archive_order_lines.status, archive_order_lines.quantity, arch_orders.payment_status, arch_orders.order_number

FROM arch_orders as orders, archive_order_lines as order_lines

WHERE arch_orders.order_number=archive_order_lines.order_number

AND archive_order_lines.prod_code LIKE '%%'

AND (arch_orders.payment_datetime LIKE '%Jan 20 2010%')

ORDER BY arch_orders.order_number ASC

 

This produces the following error:

can't find order numbers Unknown column 'archive_order_lines.status' in 'field list'

 

All of the above fields are definitely present in the tables.

The error reported always shows the first part of the query i.e.

if i changed the first part of the query to:

SELECT DISTINCT archive_order_lines.quantity, archive_order_lines.status, ...

the resulting error would say:

can't find order numbers Unknown column 'archive_order_lines.quantity' in 'field list'

 

This query works correctly with other DB tables.

 

Any ideas why this does not work?

 

(sorry, I'm not sure what version of MySQL is installed on the server)

 

[kickstart]

Hi

 

You have given the table alias names but then referred to the columns using the table names rather than the alias names, which seems a bit odd (never tried it but would assume it would cause this kind of issue).

 

All the best

 

Keith

[the_opposite]

Thanks Keith - That did the trick.

Can't believe I didn't spot that