join on navigations, navigation_products, products, price_modifiers, products_reviews and messages

gistfile1.txt

  SELECT products.sku, products.name, products.uri, products.price, products.short_description,
  COALESCE( MIN( price_modifiers.price ), products.price ) AS selling_price,
   ROUND (( products.price - MIN( price_modifiers.price ) )
     / products.price * 100 - 0.5 ) AS discount_percent,
   ROUND( AVG ( messages.rating ), 1 ) AS rating,
   inventories.quantity AS inventory
  FROM navigations navigation
  JOIN navigation_products
    ON navigation.navigation_id = navigation_products.navigation_id
  JOIN products
    ON navigation_products.sku = products.sku 
  LEFT JOIN price_modifiers
    ON (
      price_modifiers.sku = products.sku
      AND ( price_modifiers.end_date IS NULL OR price_modifiers.end_date >= '2014-12-16' )
      AND price_modifiers.quantity <= 1
      AND (
        price_modifiers.roles_id IS NULL
        OR price_modifiers.roles_id IN
          ( SELECT roles_id FROM user_roles WHERE ( user_roles.users_id = 2 ) )
      )
      AND (
        price_modifiers.start_date IS NULL OR price_modifiers.start_date <= '2014-12-16' )
    )
  LEFT JOIN product_reviews ON product_reviews.sku = products.sku
  LEFT JOIN messages
    ON (
      product_reviews.messages_id = messages.messages_id
      AND messages.approved = 't'
      AND messages.public = 't'
    )
  LEFT JOIN inventories ON products.sku = inventories.sku
  WHERE navigation.uri = 'painting-supplies'
    AND products.active = 't'
  GROUP BY navigation_products.priority, products.sku, inventories.quantity
  ORDER BY navigation_products.priority DESC, products.priority DESC