Query Pulling Wrong information from MySQL database :: Cactushop

[Andrew1986]

i have the following query

Code: Select all

strQuery = limitSQL("tblCactuShop" & TABLE_PREFIX & "Versions.V_Price, tblCactuShop" & TABLE_PREFIX & "Products.P_ID, tblCactuShop" & TABLE_PREFIX & "Products.P_DateAdded, tblCactuShop" & TABLE_PREFIX & "Categories.CAT_ID, tblCactuShop" & TABLE_PREFIX & "Products.P_Name" & numLanguageID & ", P_StrapLine" & CInt(numLanguageID) & " FROM (((tblCactuShop" & TABLE_PREFIX & "Versions INNER JOIN tblCactuShop" & TABLE_PREFIX & "Products ON tblCactuShop" & TABLE_PREFIX & "Versions.V_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink ON tblCactuShop" & TABLE_PREFIX & "Products.P_ID = tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_ProductID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "Categories ON tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink.PCAT_CategoryID = tblCactuShop" & TABLE_PREFIX & "Categories.CAT_ID) LEFT OUTER JOIN tblCactuShop" & TABLE_PREFIX & "Suppliers ON tblCactuShop" & TABLE_PREFIX & "Suppliers.SUP_ID WHERE P_Name" & CInt(numLanguageID)  & " <> '' AND V_Live ='y' AND P_Live = 'y' AND CAT_Live = 'y' AND V_CustomerGroupID IN (0," & DB_CG_ID & ") AND P_CustomerGroupID IN (0," & DB_CG_ID & ") AND CAT_CustomerGroupID IN (0," & DB_CG_ID & ") AND (SUP_Live <> 'n' OR SUP_Live IS NULL) AND P_Featured=0 ORDER BY P_ID DESC", numNewestProductsList, true)

but if i have a product with 2 version and both are different prices it pulls out both the versions. in a select query in one of the other pages it has this query

Code: Select all

strQuery = "SELECT " & strDistinctRowStatement & " " & strSelectValues & ", MIN(V_Price) As MinPrice, MIN(T_TaxRate) As MinTaxRate FROM ((((tblCactuShop" & TABLE_PREFIX & "Categories c INNER JOIN tblCactuShop" & TABLE_PREFIX & "ProductCategoryLink pcl ON c.CAT_ID = pcl.PCAT_CategoryID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "Products ON pcl.PCAT_ProductID = tblCactuShop" & TABLE_PREFIX & "Products.P_ID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "Versions v ON tblCactuShop" & TABLE_PREFIX & "Products.P_ID = v.V_ProductID) INNER JOIN tblCactuShop" & TABLE_PREFIX & "TaxRates t on v.V_Tax = t.T_ID) LEFT OUTER JOIN tblCactuShop" & TABLE_PREFIX & "Suppliers s ON tblCactuShop" & TABLE_PREFIX & "Products.P_SupplierID = s.SUP_ID WHERE P_Name" & CInt(numLanguageID)  & " <> '' AND V_Live = 'y' AND V_CustomerGroupID IN (0," & NumSafe(DB_CG_ID) & ") AND P_Live = 'y' AND NOT (P_End = 'y' AND V_Quantity < 1) AND P_CustomerGroupID IN (0," & NumSafe(DB_CG_ID) & ") AND CAT_Live = 'y' AND CAT_CustomerGroupID IN (0," & NumSafe(DB_CG_ID) & ") AND CAT_ID=" & CAT_ID & " AND (SUP_Live <> 'n' OR SUP_Live IS NULL) GROUP BY " & strSelectValues2 & " ORDER BY " & strProductsOrderBy

which pulls out the minimum priced version only how would i get my query to only pull out the lowest price version like the above select does?

[Rob]

basically cactushop products can have multiple versions, with different prices (e.g. different models of iPOD or whatever) .... but when listing products, it shows 'from $XXX', pulling out lowest price to display

Is that not the behaviour you want?

Can you please give me some more info as I am pretty confused about what behaviour you are trying to change.

If it helps, this bit in the second query does the min pricing bit

Code: Select all

strQuery = "SELECT " & strDistinctRowStatement & " " & strSelectValues & ", MIN(V_Price) As MinPrice, MIN(T_TaxRate) As MinTaxRate