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

I have a table of transactions (store_id, item_id, price). I want to find a store_id that sells at most two different items without using aggregates and groupings.

Is there a way to do this?

+3


source to share


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







All Articles