Unknown column name 'table.column' in 'field list'

Question

I'm struggling with a weird issue with MySQL while using an inner join (although I'm not sure if that's the cause of it). My issue occurs when I try to select a column from a particular table (the column is ambiguous). I'm getting a friendly little error telling me that the column doesn't exist. (Unknown column 'items.id' in 'field list')

Anyway, here's my query:

SELECT `items`.`id`, `chemical_name`, `full_name`, `amount`, `smiles`, `inchi`, `inchikey`, `average_mass`, `molecular_weight`, `monoisotopic_mass`, `nominal_mass` FROM `items` A INNER JOIN `chemicals` B ON A.csid = B.csid WHERE `owner` = '2'

Am I doing something wrong or is this some weird MySQL bug?

Thanks

Answer 1

You use the alias A for your table items.

use A.id in your select instead of items.Id and you should be fine