How to find at most two items in MySQL without aggregation and grouping
1 answer
Interesting requirements ... it would be much faster and easier with aggregate functions and groupings .. but here's another way:
SELECT DISTINCT t1.store_id
FROM
Transactions t1
LEFT JOIN Transactions t2
ON t1.store_id = t2.store_id
AND t1.item_id <> t2.item_id
LEFT JOIN Transactions t3
ON t1.store_id = t3.store_id
AND t3.item_id NOT IN (t1.item_id, t2.item_id)
WHERE t3.store_id IS NULL
The query works by joining from one store record to another record for the same store but on a different item. He then tries to join a third entry for the same store, but a different item. If he finds this entry, then the store will sell more than two items and will be excluded from the offer WHERE
.
Just to give you an idea of what a request usually looks like:
SELECT store_id
FROM Transactions
GROUP BY store_id
HAVING COUNT(DISTINCT item_id) < 3
+3
source to share