Member Avatar for cy163

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

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

  • 2 Contributors
  • 1 Reply
  • 76 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by jbisono
Member Avatar for jbisono

In SQL SERVER I believe you can do this. so maybe that can help you to organize it in mysql it should be something similar to this.

SELECT CUSTOMERID
FROM TABLE
GROUP BY CUSTOMERID
HAVING COUNT(FLOWER) >= 2
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.