Query to fetch data matching multiple values across DB rows

Question

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.

Lieven Keersmaekers · Accepted Answer · 2013-02-28 11:43:17Z

3

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
;

answered Feb 28, 2013 at 11:43

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Egor Skriptunoff · Accepted Answer · 2013-02-28 11:11:19Z

2

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

answered Feb 28, 2013 at 11:11

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Comments

user1919238 · Accepted Answer · 2013-02-28 11:02:14Z

1

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

answered Feb 28, 2013 at 11:02

user1919238

Comments

QArea · Accepted Answer · 2013-02-28 12:05:16Z

0

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')

answered Feb 28, 2013 at 12:05

QArea

5,0231 gold badge16 silver badges23 bronze badges

Collectives™ on Stack Overflow

Query to fetch data matching multiple values across DB rows

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related