Помгите с запросом в mysql

Тема в разделе "Базы данных", создана пользователем shaen, 25 май 2012.

Статус темы:
Закрыта.
Модераторы: latteo
  1. shaen

    shaen Постоялец

    Регистр.:
    23 июн 2011
    Сообщения:
    51
    Симпатии:
    9
    Доброе утро!!!

    Помогите с запросом в mysql
    Код:
    SELECT
      rc_shop_products.*, rc_shop_product_descriptions.*,
      IF(
        (SELECT rc_shop_customer_discounts.discount FROM rc_shop_customer_discounts WHERE rc_shop_customer_discounts.customer_id = 1810 AND rc_shop_customer_discounts.category_id = rc_shop_products.id AND rc_shop_customer_discounts.is_group = 0) IS NULL,
       
        (SELECT rc_shop_customer_discounts.discount FROM rc_shop_customer_discounts WHERE rc_shop_customer_discounts.customer_id = 1810 AND rc_shop_customer_discounts.category_id = rc_shop_products.id AND rc_shop_customer_discounts.is_group = 0),
        (SELECT rc_shop_customer_discounts.discount FROM rc_shop_customer_discounts WHERE rc_shop_customer_discounts.customer_id = 1810 AND rc_shop_customer_discounts.category_id = rc_shop_products.category_id AND rc_shop_customer_discounts.is_group = 1)
      ) as discount,
     
      // Дальнейшая конструкция из IF'ов не работает привожу ее для понимания что мне необходимо добиться, мне нужно проверить discount на наличие в нем значения и на основе этой проверки рассчитать скидку для товара.
     
      IF((discount IS NULL),
        (rc_shop_prices.price),
        (ROUND(CEIL((((rc_shop_prices.price / 100) * discount) + rc_shop_prices.price)*100)/100, 2))
      ) price,
     
      IF((discount IS NULL),
        (rc_shop_prices.price * rc_shop_product_descriptions.places_in)
        (ROUND(CEIL((((rc_shop_prices.price / 100) * discount) + rc_shop_prices.price)*100)/100 * rc_shop_product_descriptions.places_in, 2))
      ) p_price
     
     
    FROM rc_shop_products
    LEFT JOIN `rc_shop_product_descriptions` ON `rc_shop_product_descriptions`.`product_id` = `rc_shop_products`.`id`
    LEFT JOIN `rc_shop_prices` ON `rc_shop_prices`.`product_id` = `rc_shop_products`.`id` AND rc_shop_prices.price_id = 47
     
    WHERE rc_shop_products.category_id = 20
     
  2. shaen

    shaen Постоялец

    Регистр.:
    23 июн 2011
    Сообщения:
    51
    Симпатии:
    9
    Кому интересно сделал так:


    Код:
    SELECT
      rc_shop_products.*, rc_shop_product_descriptions.*,
     @DISCOUNT :=  IF(
        (SELECT rc_shop_customer_discounts.discount FROM rc_shop_customer_discounts WHERE rc_shop_customer_discounts.customer_id = 1810 AND rc_shop_customer_discounts.category_id = rc_shop_products.id AND rc_shop_customer_discounts.is_group = 0) IS NULL,
     
        (SELECT rc_shop_customer_discounts.discount FROM rc_shop_customer_discounts WHERE rc_shop_customer_discounts.customer_id = 1810 AND rc_shop_customer_discounts.category_id = rc_shop_products.id AND rc_shop_customer_discounts.is_group = 0),
        (SELECT rc_shop_customer_discounts.discount FROM rc_shop_customer_discounts WHERE rc_shop_customer_discounts.customer_id = 1810 AND rc_shop_customer_discounts.category_id = rc_shop_products.category_id AND rc_shop_customer_discounts.is_group = 1)
      ) as discount,
     
      IF((@DISCOUNT IS NULL),
        (rc_shop_prices.price),
        (ROUND(CEIL((((rc_shop_prices.price / 100) * @DISCOUNT) + rc_shop_prices.price)*100)/100, 2))
      ) as price,
     
      IF((@DISCOUNT IS NULL),
        (rc_shop_prices.price * rc_shop_product_descriptions.places_in),
        (ROUND(CEIL((((rc_shop_prices.price / 100) * @DISCOUNT) + rc_shop_prices.price)*100)/100 * rc_shop_product_descriptions.places_in, 2))
      ) as p_price
     
     
    FROM rc_shop_products
    LEFT JOIN `rc_shop_product_descriptions` ON `rc_shop_product_descriptions`.`product_id` = `rc_shop_products`.`id`
    LEFT JOIN `rc_shop_prices` ON `rc_shop_prices`.`product_id` = `rc_shop_products`.`id` AND rc_shop_prices.price_id = 47
     
    WHERE rc_shop_products.category_id = 20
     
Статус темы:
Закрыта.