You might try UNION with the 1st statement pulling all products with groupid = 0 and the 2nd pulling 1 product with groupid > 1.
-----Original Message----- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, October 10, 2005 3:31 PM To: MySQL List Subject: [SPAM] - Query help - Bayesian Filter detected spam 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
