Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Need help: design a query to extract multiple order information

Discussion in 'Information Technology' started by 82033chn, Jul 28, 2006.

  1. 82033chn

    82033chn
    Expand Collapse
    Guest

    Suppose I have a customer_order table where customer's order details
    are stored, e.g., Customer I ordered Product A, B, C on day 1, and
    Customer II ordered Product B, C, F, G on day 2, and so on.

    I could not figure out an "easy" way which a single query can be
    designed so I know which customers ordered Product B and C at the same
    time, other than doing it awkardly by:

    SELECT DISTINCT customer_order.CustomerID, customer_order.Product,
    customer_order_1.Product
    FROM customer_order INNER JOIN customer_order AS customer_order_1 ON
    customer_order.Product = customer_order.Product
    WHERE (((customer_order.Product)="B") AND
    ((customer_order_1.Product)="C"));

    What if I need to know the customers who ordered seven products with a
    single order? I hate to design a query where the table is linked 7
    times! Surely there is a simpler way? Appreciate any help. Thanks.
    Please email to huangtj@gmail.com.
     
  2. Loading...

    Similar Threads Forum Date
    Interracial And Interfaith Marriage, Help Needed To Confront/convince My Parents Love & Marriage Aug 16, 2016
    17 yr old Baljit needs your help Get Involved Aug 19, 2015
    Learn Punjabi Need help Language, Arts & Culture May 20, 2014
    United Sikhs From United Sikhs. Phillippines Needs Your Help Sikh Organisations Nov 22, 2013
    Need Help... (Keeping Hair) Questions and Answers Sep 2, 2013

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Are you saying you want to know which customers ordered a specific 7
    products?

    SELECT customer_order.CustomerID, customer_order.OrderID
    FROM customer_order
    WHERE customer_order.Product IN ("B", "C", "E", "F", "G", "I", "K")
    GROUP BY customer_order.CustomerID, customer_order.OrderID
    HAVING Count(*) = 7

    Or are you saying that you want to know which customers order exactly 7
    products (any 7 products)?

    SELECT customer_order.CustomerID, customer_order.OrderID
    FROM customer_order
    GROUP BY customer_order.CustomerID, customer_order.OrderID
    HAVING Count(*) = 7

    In the second query, change it to >= 7 if you want those customers who
    ordered at least 7 products.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "82033chn" <huangtj@gmail.com> wrote in message
    news:1147801804.098940.112490@i39g2000cwa.googlegroups.com...
    > Suppose I have a customer_order table where customer's order details
    > are stored, e.g., Customer I ordered Product A, B, C on day 1, and
    > Customer II ordered Product B, C, F, G on day 2, and so on.
    >
    > I could not figure out an "easy" way which a single query can be
    > designed so I know which customers ordered Product B and C at the same
    > time, other than doing it awkardly by:
    >
    > SELECT DISTINCT customer_order.CustomerID, customer_order.Product,
    > customer_order_1.Product
    > FROM customer_order INNER JOIN customer_order AS customer_order_1 ON
    > customer_order.Product = customer_order.Product
    > WHERE (((customer_order.Product)="B") AND
    > ((customer_order_1.Product)="C"));
    >
    > What if I need to know the customers who ordered seven products with a
    > single order? I hate to design a query where the table is linked 7
    > times! Surely there is a simpler way? Appreciate any help. Thanks.
    > Please email to huangtj@gmail.com.
    >
     
  4. 82033chn

    82033chn
    Expand Collapse
    Guest

    Neither, Doug. Sorry that I did not make myself clear earlier. What I'm
    trying to do was finding customers who ordered several specific
    products in a single order (the customer may have ordered more products
    in that order than I qurried for). For example, Customer I ordered
    Product A, B, C, D on day 1, and Customer II ordered Product B, C, F, G
    on day 2. If I want to know who ordered B&C regardless of order date,
    the answer is Customer I and II. But if I want to know who ordered A,B,
    and C, then the answer should just be Customer I. How am I going to do
    that? Thanks.
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    I don't really see how that's different than the first example I gave.

    SELECT customer_order.CustomerID, customer_order.OrderID
    FROM customer_order
    WHERE customer_order.Product IN ("B", "C")
    GROUP BY customer_order.CustomerID, customer_order.OrderID
    HAVING Count(*) = 2

    would give you Customer I and Customer II

    SELECT customer_order.CustomerID, customer_order.OrderID
    FROM customer_order
    WHERE customer_order.Product IN ("A", "B", "C")
    GROUP BY customer_order.CustomerID, customer_order.OrderID
    HAVING Count(*) = 3

    would give you Customer I.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "82033chn" <huangtj@gmail.com> wrote in message
    news:1147828994.825090.198040@j73g2000cwa.googlegroups.com...
    > Neither, Doug. Sorry that I did not make myself clear earlier. What I'm
    > trying to do was finding customers who ordered several specific
    > products in a single order (the customer may have ordered more products
    > in that order than I qurried for). For example, Customer I ordered
    > Product A, B, C, D on day 1, and Customer II ordered Product B, C, F, G
    > on day 2. If I want to know who ordered B&C regardless of order date,
    > the answer is Customer I and II. But if I want to know who ordered A,B,
    > and C, then the answer should just be Customer I. How am I going to do
    > that? Thanks.
    >
     
  6. 82033chn

    82033chn
    Expand Collapse
    Guest

    Hi Doug,

    I used your example without grouping the orderID and that's where I
    made my mistake. All is clear now and thanks a bunch!

    This is also the first time I see the IN ("A", "B") expression and I
    figure it is the same as using "A" OR "B" expression. I searched the
    Access Help and did not find it. Is it a function and are there other
    useful functions like that?

    Cheers!
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    IN is part of the SQL language.

    You might find the following useful. (Don't worry that they say Access 2000:
    they apply to all versions)

    http://msdn.microsoft.com/library/en-us/dnacc2k/html/acfundsql.asp
    http://msdn.microsoft.com/library/en-us/dnacc2k/html/acintsql.asp
    http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "82033chn" <huangtj@gmail.com> wrote in message
    news:1147898030.799440.275140@i39g2000cwa.googlegroups.com...
    > Hi Doug,
    >
    > I used your example without grouping the orderID and that's where I
    > made my mistake. All is clear now and thanks a bunch!
    >
    > This is also the first time I see the IN ("A", "B") expression and I
    > figure it is the same as using "A" OR "B" expression. I searched the
    > Access Help and did not find it. Is it a function and are there other
    > useful functions like that?
    >
    > Cheers!
    >
     

Share This Page