Mysql – Select row with more of one type than another in a separate table

MySQL

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'
) 
>
(
  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 < p2.id
      )
      WHERE p1.type = 'apple'
      AND p2.id IS NULL

Best Answer

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') > SUM(p.type='pear')

Related Solutions

Select from SQL in phpMyAdmin – How to Guide

You seem to want to GROUP BY customer and date; and I guess that you want to concatenate the books.

This would do it:

 SELECT
     customer_id, customer_name, `Date`, 
     GROUP_CONCAT(`Books_bought` ORDER BY `Books_bought` SEPARATOR ', ') AS books
 FROM
     t
 GROUP BY
     customer_id, customer_name , `Date`
 ORDER BY
     `Date`, customer_id, customer_name ;

 customer_id | customer_name | Date       | books
 ----------: | :------------ | :--------- | :--------------------------
           1 | Alex          | 08.06.2017 | Harry Potter2, HarryPotter1
           1 | Alex          | 09.06.2017 | PHP 7
           2 | Sam           | 10.06.2017 | SQL

Check:

dbfiddle here

Mysql – Trying to reduce number of table scans when querying on the same column but different values

Try the following:

SELECT o1.customerNumber
FROM orders o1
INNER JOIN orderdetails od1
ON o1.orderNumber = od1.orderNumber
INNER JOIN orders o2
ON o2.customerNumber = o1.customerNumber
INNER JOIN orderdetails od2
ON o2.orderNumber = od2.orderNumber
WHERE od1.productCode = 'S18_1589'
AND od2.productCode = 'S24_1628'
GROUP BY o1.customerNumber;

Describing the logic in words, this will find all orders for the first product number, then require there also be orders for the same customerNumber that has ordered the second product number. (This logic is NOT meant to imply the order the optimizer will choose joins. It is only the logic used to construct the query, based on the requirements stated.)

I dropped the ORDER BY because GROUP BY includes an order by as part of what it does. Although, the optimizer likely would already know it is ordered, and so it is just syntax difference.

As for whether this is more efficient, I would benchmark it to see. For larger data sets I believe my solution will be better than an OR based one, but again, always benchmark.

Related Question