How to find at most two items in MySQL without aggregation and grouping

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