CODEWITHSUNDEEP
sqloracle-database
jcd
jcd

Reputation: 300

Finding if there's at least one attribute in common in a set

I'm trying to do a validation using an Oracle SQL query, and the idea is:

There are items, and to every item there could be more than one suppliers.

I have a set of items (in a shopping bag) and want to verify if there is at least one common supplier between them. That means that all the items in the bag share at least one supplier.

Simplifying, the tables are:

BAG table with bag_id and item columns.
SUPPLY table with item and supplier columns.

Thank you!

Upvotes: 0

Views: 680

Answers (2)

PinnyM
PinnyM

Reputation: 35541

This should give you a list of suppliers that match more than one item, with the most common suppliers on top

SELECT SUPPLY.supplier, COUNT(SUPPLY.item) item_count
FROM BAG
JOIN SUPPLY
  ON BAG.item = SUPPLY.item
GROUP BY SUPPLY.supplier
HAVING COUNT(SUPPLY.item) > 1
WHERE BAG.bag_id = ? --substitute bag_id
ORDER BY COUNT(SUPPLY.item) DESC

If you need to find suppliers that match ALL items in the bag, then use this query:

SELECT SUPPLY.supplier
FROM BAG
JOIN (SELECT bag_id, COUNT(*) as item_count FROM BAG GROUP BY bag_id WHERE bag_id = ?) bag_count
  ON BAG.id = bag_count.bag_id
JOIN SUPPLY
  ON BAG.item = SUPPLY.item
GROUP BY SUPPLY.supplier
HAVING COUNT(SUPPLY.item) = bag_count.item_count
WHERE BAG.bag_id = ?

You can check if the list is empty to determine if no common suppliers exist by wrapping this with an EXISTS statement.

Upvotes: 1

Alexander Tokarev
Alexander Tokarev

Reputation: 1045

select case when exists
                       (
                        select 1
                        from bag b
                             inner join item i on i.id = b.item_id
                             inner join supplier s on s.id = i.supplier_id
                                                   and s.is_main = 'Y'
                       )
             then 'Y'
             else 'N'   
       end contains_main_supplier                   
from dual

Upvotes: 0

Related Questions