How to do this query

cy163 · #1 (**permalink**) 10-03-10, 23:02

Hi ALL

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

Code:

CustomerID        Flower

John                peony
John                lily
John                Lotus

Mary               peony
Mary               lily
Mary               chrysanthemum

Lisa                chrysanthemum
Lisa                peony
Lisa                kapok

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

it-iss.com · #2 (**permalink**) 10-04-10, 03:27

Hi,

if the column CustomerID and Flower is unique you could always use

Code:

SELECT CustomerID 
   FROM table 
  WHERE Flower IN ('peony','lily')
GROUP BY CustomerID
HAVING Count(*) = 2

Internet Services

Web Development

Digital Marketing

Consumer Tech