CODEWITHSUNDEEP
sqloracle-database
user2119105
user2119105

Reputation: 3

Query to fetch data matching multiple values across DB rows

Hi I need help on a sql query. The result must match values for a single column across the rows. Here is an example. I need to find out store(s) that must have all of these items for sale: Books, Stationery, and Toys.

Store Items
----- --------
AA     PERFUMES
AA     TOYS
BB     STATIONERY
BB     BOOKS
BB     TOYS

In the example above, "BB" is the only store that matches all of our criteria and hence the result expected from the query.

I tried query with AND operator (select store from storeitem where items = 'books' and items ='toys' and items='stationery';) and it did not work as it expects all values in the same row and with in operator (select store from storeitem where items in ('books','stationery','toys');) , this doesn't follow must match all values criteria. Need your help on this.

Upvotes: 0

Views: 4067

Answers (4)

QArea
QArea

Reputation: 4981

If I correctly understand your question, you needed that query:

Select store from storeitem where store in (select store from storeitem where items = 'books')

AND store in (select store from storeitem where items ='toys')

AND store in (select store from storeitem where items='stationairy')

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You could skip using subqueries alltogether and use a HAVING DISTINCT clause to return the stores you need.

SELECT  store, COUNT(*)
FROM    your_table
WHERE   items in ('STATIONAIRY', 'BOOKS', 'TOYS')
GROUP BY
        store
HAVING  COUNT(DISTINCT items) = 3
;

Example

WITH your_table as (
  SELECT 'AA' as Store, 'PERFUMES' as Items FROM dual UNION ALL
  SELECT 'AA', 'TOYS' FROM dual UNION ALL
  SELECT 'BB', 'STATIONAIRY' FROM dual UNION ALL
  SELECT 'BB', 'BOOKS' FROM dual UNION ALL
  SELECT 'BB', 'TOYS' FROM dual
)
SELECT  store, COUNT(*)
FROM    your_table
WHERE   items in ('STATIONAIRY', 'BOOKS', 'TOYS')
GROUP BY
        store
HAVING  COUNT(DISTINCT items) = 3
;

Upvotes: 3

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23727

select store
from (
  select distinct store, items
  from your_table
  where items in ('books','stationery','toys')
)
group by store
having count(0) = 3

Upvotes: 2

user1919238
user1919238

Reputation:

This is the general approach that should work (not tested on Oracle specifically):

select store from (
  select store,
         max(case when items = 'stationery' then 1 else 0 end) as has_stationery,
         max(case when items = 'books'      then 1 else 0 end) as has_books,
         max(case when items = 'toys'       then 1 else 0 end) as has_toys
    from your_table
    group by store
  ) as stores_by_item
  where has_stationery = 1 and has_books = 1 and has_toys = 1

Upvotes: 1

Related Questions