CODEWITHSUNDEEP
mysqlsubquerywhere-clause
capte
capte

Reputation: 207

Mysql Subquery result in the where clause

I'm trying to get the result of the subquery sale and use it in the WHERE clause ... I want to exclude sales that are equal to 1

Is there a way to do this ?

SELECT 
  p.product_id,
  etp_vendor.active,
  (SELECT AVG(rating) AS total FROM etp_review r1 WHERE r1.product_id = p.product_id AND r1.`status` = '1' GROUP BY r1.product_id) AS rating,
  (SELECT etp_category.sale FROM etp_category WHERE etp_category.category_id = etp_product_to_category.category_id ORDER BY etp_category.sale LIMIT 1) AS sales,
  (SELECT pd2.price FROM etp_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND (pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW()) ORDER BY pd2.priority, pd2.price LIMIT 1) AS discount,
  (SELECT ps.price FROM etp_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND (ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()) ORDER BY ps.priority, ps.price LIMIT 1) AS special
FROM
  etp_product p
  LEFT OUTER JOIN etp_product_description pd ON (p.product_id = pd.product_id)
  LEFT OUTER JOIN etp_product_to_store p2s ON (p.product_id = p2s.product_id)
  INNER JOIN etp_product_to_vendor ON (p.product_id = etp_product_to_vendor.product_id)
  INNER JOIN etp_product_to_category ON (p.product_id = etp_product_to_category.product_id)
  INNER JOIN etp_vendor ON (etp_product_to_vendor.vendor_id = etp_vendor.vendor_id)
WHERE
  pd.language_id = '1' AND 
  p.`status` = '1' AND 
  etp_vendor.active = '1' AND 
  p.date_available <= NOW() AND 
  p2s.store_id = '0'
GROUP BY
  p.product_id
ORDER BY
  p.sort_order,
  LCASE(pd.name)

Upvotes: 0

Views: 166

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Yes you can use HAVING clause

GROUP BY
  p.product_id
HAVING sales != 1 /*  or HAVING sales <> 1*/
ORDER BY
  p.sort_order,
  LCASE(pd.name)

Upvotes: 2

Related Questions