MySQL - conditional SELECT

Question

I have tables item and store (it's a store management system). item table has a column called store_id and another column called status. item.status can be 'sold' or 'unsold'.

I need help writing a query which will do these things:

• select all items of all stores
• if a store has just one item and that item is 'sold', remove that item from the result set

Thanks in advance!

Answer 1

You could create a filtering subquery that searches for stores with more than one item, or one unsold item. Then you can join the subquery on the original tables, like:

select  *
from    (
        select  s2.store_id
        from    store s2
        join    items i2
        on      s2.store_id = i2.store_id
        group by
                s2.store_id
        having 
                count(*) > 1 -- More than one item
                or max(i2.status) = 'unsold' -- One item but unsold
        ) filter
join    store s
on      filter.store_id = s.store_id
join    items i
on      s.store_id = i.store_id