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.

Answer: 
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
;

```
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

```