The following SQL code is using outer joins to get the information from a main items table as well as 5 foreign tables, but in addition to this I need to output a column from one other foreign table, ie. the subcategories table.
$select = 'SELECT itemID, itemTitle, itemSKULadies, itemSKUMen, itemDescLadies, itemDescMen, itemPrice, itemColours, categories.category, suppliers.supplier, itemTypes.itemType, sizesMen.size AS Msize, sizesLadies.size AS Lsize, subcategories.subcategory'; $from = ' FROM items LEFT JOIN categories ON categories.catID=items.catID LEFT JOIN suppliers ON suppliers.supplierID=items.supplierID LEFT JOIN itemTypes ON itemTypes.itemTypeID=items.itemTypeID LEFT JOIN sizesMen ON sizesMen.sizeMenID=items.sizeMenID LEFT JOIN sizesLadies ON sizesLadies.sizeLadiesID=items.sizeLadiesID LEFT JOIN item_to_subcat ON subcategories.subcatID=item_to_subcat.subcatID'; $where = ' WHERE TRUE';
This table has a many to many relationship with the main items table so instead of the foreign key being stored in the items table (as they are for the other 5 foreign keys), this table has its own look up table called item_to_subcat.
So I need to extend the SQL to output the subcategory from the subcategories table. I tried to do this in the code below by firstly adding the following to the end of the select part of the code:
Then I added another left join as follows onto the from part of the code:
LEFT JOIN item_to_subcat ON subcategories.subcatID=item_to_subcat.subcatID
When I tested I got the following error:
Error fetching items: Column ‘itemID’ in field list is ambiguous
Does anyone what this could mean or how to fix this?