Not sure that the title really describes what I am doing here but basically as follows,
Query selecting prodHead (basic product details such as prod code and description)
Joining prodSku (multiple records per prodHead to allow for variants such as colours, size, price)
prodMenuIndex (ndex of prodHead records in the current menu section being viewed.
As the prodSku has multiple option I just want to limit the selection to 1 (the cheapest based on prodsku.net) but as you will see from the attached query it will bring back all associated prodSku records.
Any help appreciated
INNER JOIN prodHead
ON prodMenuIndex.prodCode = prodHead.prodCode
INNER JOIN prodSku
ON prodHead.prodCode = prodSku.prodCode
WHERE prodMenuIndex.menuId = #currentMenu#
AND ProdHead.Avail = 1
AND prodSku.Avail = 1
ORDER BY #sortOrder#
<cfif IsDefined("url.StartRow") and IsDefined("ResultsPerPage")>
LIMIT #url.StartRow#, #ResultsPerPage#