Hi, I have this query below, and I have been pulling my hair out for the
past couple of hours trying to get it to do what I want. As is, it
works, but I need it to consider other conditions. One of the columns
in the products table is called groupid. I need it to pull all products
with a groupid of 0 and only 1 product with a groupid > 0 (doesn't
matter which one). Any help will save my sanity. ;)
SELECT products.*, MIN(pricing.price) as price , products_lng.product as
product_lng, products_lng.descr as descr_lng, products_lng.full_descr as
fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant,
IF(classes.classid IS NOT NULL,'Y','') as is_product_options,
MIN(v_pricing.price) as v_price, products_lng.product as product_lng,
products_lng.descr as descr_lng, products_lng.full_descr as
fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant,
IF(classes.classid IS NOT NULL,'Y','') as is_product_options,
MIN(v_pricing.price) as v_price FROM products, pricing ,
products_categories, categories LEFT JOIN products_lng ON
products_lng.productid = products.productid AND products_lng.code = 'US'
LEFT JOIN classes ON classes.productid = products.productid LEFT JOIN
variants ON variants.productid = products.productid LEFT JOIN pricing as
v_pricing ON v_pricing.variantid = variants.variantid AND
v_pricing.quantity = 1 AND v_pricing.membership IN ('','') WHERE
pricing.productid=products.productid AND pricing.quantity=1 AND
pricing.membership IN ('','') AND products.product_type <> 'C' AND
products.product_type <> 'B' AND (pricing.variantid = 0 OR
(variants.variantid = pricing.variantid AND variants.avail > 0)) AND
products_categories.productid=products.productid AND
products_categories.categoryid = categories.categoryid AND
categories.membership IN ('','') AND
products_categories.categoryid='412' AND (products_categories.main='Y'
OR products_categories.main!='Y') AND products.forsale='Y' AND
(products.avail>0 OR products.product_type NOT IN ('','N')) GROUP BY
products.productid ORDER BY products_categories.orderby ASC,
products.product ASC LIMIT 10, 10
--
John C. Nichel
KegWorks.com
716.856.9675
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]