Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007

    Unanswered: How to do this query

    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.

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain

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

    SELECT CustomerID 
       FROM table 
      WHERE Flower IN ('peony','lily')
    GROUP BY CustomerID
    HAVING Count(*) = 2
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts