How to do this query

Question

Hi ALL

THere is a data table from a flower shop which looks like

 CustomerID        Flower

    John                peony
    John                lily
    John                Lotus

    Mary               peony
    Mary               lily
    Mary               chrysanthemum

    Lisa                chrysanthemum
    Lisa                peony
    Lisa                kapok

enter code here

I would like to find those customers' ID who buy the same n flowers. For example, in the above table, for peony and lily , the customers who buy both of them (n=2, for this case) are John and Mary.

I cannot figure out a SQL statement to do the above query. Please help. Thanks

Answer 1

Select Customer ID, Flower from [yourtable] where Flower = 'peony' or 'lily'

Do you want it to be dynamic?

then maybe make a stored procedure with variables. More info about sp's here

Answer 2

How about a self-join?

SELECT 
    y.CustomerID 
FROM 
    YourTable y 
JOIN 
    YourTable y2 
ON 
    y.CustomerID = y2.CustomerId 
WHERE
    y.Flower = "Lily"
AND
    y2.Flower = "Lotus"

Answer 3

Join the table with itself.

SELECT a.CustomerID, b.CustomerID, a.Flower FROM flowertable a, flowertable b WHERE a.Flower = b.Flower

Answer 4

you need something like this

select distinct(CustomerID) from mytable 
where flower in
(select distinct(flower) from mytable group  by  flower having count(flower) = 2)

you can replace 2 to any number you want !

Answer 5

I would try an exists clause that would only report flowers that have more than one distinct purchaser:

select a.customerid, a.flower
from yourtable a
where exists
  (select 'x'
   from yourtable b 
   where b.customerid <> a.customerid
   and b.flower = a.flower)