Using INNER JOIN on a Search Page
I have discovered that, when including an INNER JOIN in the SQL, it will not return any results, even though there are two records with the variable 'Test Name' in the Table.
This is my SQL, that brings no results...
SELECT menu_mnu.id_mnu, idmnu_mnu, menu_mnu.name_mnu, menu_mnu.price, menu_mnu.saleprice, menu_mnu.tradeprice, menu_mnu.onStatus, menu_mnu.trade_only, menu_mnu.linktopage, menu_mnu.type_mnu, menu_mnu.order_bak, menu_mnu.inStock, menu_mnu.LoyaltyType, product_images.filename
FROM menu_mnu INNER JOIN product_images ON product_images.product_id = menu_mnu.id_mnu
WHERE menu_mnu.name_mnu = 'Test Name'
ORDER BY menu_mnu.order_bak
This is my SQL, that brings results...
SELECT menu_mnu.id_mnu, idmnu_mnu, menu_mnu.name_mnu, menu_mnu.price, menu_mnu.saleprice, menu_mnu.tradeprice, menu_mnu.onStatus, menu_mnu.trade_only, menu_mnu.linktopage, menu_mnu.type_mnu, menu_mnu.order_bak, menu_mnu.inStock, menu_mnu.LoyaltyType
FROM menu_mnu
WHERE menu_mnu.name_mnu = 'Test Name'
ORDER BY menu_mnu.order_bak
However, I have to use the INNER JOIN function as I am referencing the record's image which is stored in the separate table. The product_image table might store multiple images for the record (related by the foreign key). Then images might be stored in the user's preferred order and by using the (first) SQL this would return the first image for that record. I have this working fine in MYSQL (old DW code).
Any Ideas?