Select row with more of one type than another in a separate table I have two tables.



I'm attempting to select customers that have bought more of one type of product than another with at least one of the first type. Let's say for example that I want to get all customers that have purchased more apples than pears but have bought at least one apple.

So far I've come up with the following query:

select customers.id 
from customers
where 'apple' in 
(
  select type 
  from products 
  where products.customer_id = customers.id
)
and 
(
  select count(p1.id) 
  from products p1 
  where p1.customer_id = customers.id 
  and p1.type = 'apple'
) 
&gt;
(
  select count(p2.id) 
  from products p2 
  where p2.customer_id = customers.id 
  and p2.type = 'pear'
)


This query works as is, however is slow, I understand that the subquerys are slowing down the query but haven't been able to find a solution involving joins that is faster for example.

SELECT c.id
      FROM customers as c
      JOIN products p1 ON (c.id = p1.customer_id)
      LEFT OUTER JOIN products p2 ON (
         c.id = p2.customer_id
         AND p2.type = 'pear'
         AND p1.id &lt; p2.id
      )
      WHERE p1.type = 'apple'
      AND p2.id IS NULL
I want to get all customers that have purchased more apples than pears but have bought at least one apple.


SELECT c.id
FROM customers as c
JOIN products p ON c.id = p.customer_id
WHERE p.type IN ('apple', 'pear')
GROUP BY c.id
HAVING SUM(p.type='apple') &gt; SUM(p.type='pear')