I have STORE_TABLE

and TRANSACTION_TABLE

With oracle, what query is to find the product that sold in all store?

CodePudding user response：

You can use a PARTITIONed OUTER JOIN:

SELECT t.item
FROM   store_table s
       LEFT OUTER JOIN transaction_table t
       PARTITION BY (t.item)
       ON (s.id = t.store)
GROUP BY
       t.item
HAVING COUNT(t.store) = COUNT(s.id)

Which, for the sample data:

CREATE TABLE store_table (id, store_name) AS
SELECT 1, 'STORE A' FROM DUAL UNION ALL
SELECT 2, 'STORE B' FROM DUAL UNION ALL
SELECT 3, 'STORE C' FROM DUAL;

CREATE TABLE TRANSACTION_TABLE (id, tran_id, store, item, qty) AS
SELECT 1, '001', 1, 'ruler',   2 FROM DUAL UNION ALL
SELECT 2, '002', 2, 'book',    5 FROM DUAL UNION ALL
SELECT 3, '003', 1, 'pencil', 10 FROM DUAL UNION ALL
SELECT 4, '004', 1, 'tissue',  3 FROM DUAL UNION ALL
SELECT 5, '005', 2, 'tissue',  5 FROM DUAL UNION ALL
SELECT 6, '006', 3, 'tissue',  2 FROM DUAL UNION ALL
SELECT 7, '007', 1, 'pencil',  5 FROM DUAL;

Outputs:

ITEM
tissue

db<>fiddle here

CodePudding user response：

You could do it without any joins.

• count how many unique stores sell each item
• include items only when that value equals the number of stores that exist

SELECT
  item
FROM
  transaction_table
GROUP BY
  item
HAVING
  COUNT(DISTINCT store) = (SELECT COUNT(*) FROM store_table)

• The DISTINCT is only necessary if a store can have multiple transactions for the same item

Demo, borrowed from @MT0

• https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=118bf2f58c6ac116c311a6947b2eff13