Proper Oracle SQL to identify primary keys that share a combination of values-CodePudding

There is most likely a simple solution to this question, but I've found myself stuck and can't find another question similar to this that's been posted. So, I have a table (SUPPLIERS) that identifies stores and their suppliers.

StoreID	SupplierID
123456	001
123456	002
123456	003
234567	001
345678	001
345678	002
456789	001
456789	004

I need to identify the stores where a specified supplier was used in combination with a specified group of suppliers. So from the above SUPPLIERS table, I need to obtain all Store IDs where supplier 001 was used with 002 AND/OR 003.

I've tried...

SELECT
   DISTINCT StoreID
FROM
   SUPPLIERS
WHERE 
     SupplierID = 001
AND (SupplierID = 002
      or
     SupplierID = 003);

...but I'm not returning what I'd expect. From the above I would expect:

StoreID
123456
345678

Thanks in advance for your time.

CodePudding user response：

Here's one option:

SQL> select distinct a.storeid
  2  from suppliers a
  3  where a.supplierid = '001'
  4    and exists (select null
  5                from suppliers b
  6                where b.storeid = a.storeid
  7                  and b.supplierid in ('002', '003')
  8               );

   STOREID
----------
    123456
    345678

SQL>