Vfrdtyky

Having a bit of trouble when trying to figure out how to return a query of a customer who ordered both A and B

What I'm looking for is all customers who order both product A and product B

SELECT CustomerID 

FROM table

WHERE product in ('a','b')

GROUP BY customerid

HAVING COUNT(distinct product) = 2

I don't normally post code only answers but there isn't a lot that words can add to this- the query predominantly explains itself

You can also

HAVING max(product) <> min(product)

It may be worth pointing out that in queries, the WHERE is performed, filtering to just products A and B. Then the GROUP BY is performed, grouping customer and counting the distinct number of products (or getting the min and max). Then the HAVING is performed, filtering to just those with 2 distinct products (or getting only those where MIN i.e. A, is different to MAX i.e. B)

If you'v never encountered HAVING, it is logically equivalent to:

SELECT CustomerID

FROM(

    SELECT CustomerID, COUNT(distinct product) as count_distinct_product

    FROM table

    WHERE product in ('a','b')

    GROUP BY customerid

)z

WHERE

     z.count_distinct_product = 2

In a HAVING clause you can only refer to columns that are mentioned in the group by. You can also refer to aggregate operations (such as count/min/max) on other columns not mentioned in the group by

I have never worked with SQLLite, but since it's specs say it is a Relational Database, it should allow the following query.

select CustomerID

  from table t

 where exists (

       select *

         from table

        where CustomerID = t.CustomerID

          and Product  = 'A'

       )

   and exists (

       select *

         from table

        where CustomerID = t.CustomerID

          and Product  = 'B'

       )

I'd use a correlated sub-query with a HAVING clause to scoop in both products in a single WHERE clause.

SELECT

  t.Customer

FROM

  @t AS t

WHERE

  EXISTS

    (

      SELECT

        1

      FROM

        @t AS s

      WHERE

        t.Customer = s.Customer

        AND s.Product IN ('A', 'B')

      HAVING

        COUNT(DISTINCT s.Product) = 2

    )

GROUP BY

  t.Customer;

Select customerid from table group by customerid having product like 'A' and product like 'B' or
you can try having count(distinct product) =2this seems to be more accurate.
The whole idea is in a group of customerid suppose 1 if I have several A's and B's count(distinct product) will give as 2 else it will be 1 so the answer is as above.

Another way I just figured out was

SELECT CustomerID 

FROM table

WHERE product in ('a','b')

GROUP BY customerid

HAVING sum(case product ='a' then 1 else 0 end) > 0

    and sum(case when product ='b' then 1 else 0 end) > 0