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

    Unanswered: How to do this query

    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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    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
  •