By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

How to select only customers that ordered 2 specific products?

P: n/a
Dear all,

I'm trying to compose a query, but I don't know how to express the statement.
I have 4 tables: Customers, Orders, Order Details and Products.
Each customer has many orders and each order may include many products.
Let's say that querying Orders and Order Details, we have the following data:
CustomerID ProductID
1 1
1 2
1 3
2 1
2 5
3 1
3 2
4 2
I'd like to display ONLY the customers that ordered BOTH Product 1 AND Product 2!
I don't like to include customers that ordered only one of the 2 products!
The result I'd like to get is:
CustomerID
1
3

Please help to express this sql query...

Thanks in advance,
John
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
John,

When you create a query, each column is evaluated as 'And' so with this in mind,
put CustomerID in the first column, and ProductID in column 2 AND column 3. Set
the criteria for column 2 as 1 and the criteria for column 3 as 2. Your query
now reads -- 'Return all customers who ordered ProductID 1 AND who ordered
ProductID 2.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"JohnA" <ja**********@yahoo.com> wrote in message
news:b4**************************@posting.google.c om...
Dear all,

I'm trying to compose a query, but I don't know how to express the statement.
I have 4 tables: Customers, Orders, Order Details and Products.
Each customer has many orders and each order may include many products.
Let's say that querying Orders and Order Details, we have the following data:
CustomerID ProductID
1 1
1 2
1 3
2 1
2 5
3 1
3 2
4 2
I'd like to display ONLY the customers that ordered BOTH Product 1 AND Product 2! I don't like to include customers that ordered only one of the 2 products!
The result I'd like to get is:
CustomerID
1
3

Please help to express this sql query...

Thanks in advance,
John

Nov 12 '05 #2

P: n/a
Thanks for your reply!

I've already tried your suggestion, but it doesn't work and I think
that's right.

As you sad, if I set 2 columns of a query as the same field and then set
criteria for both columns, that's evaluated as AND! So how is it
possible a product to be equal to 1 AND 2 simultaneously? It doesn't
work this way.
And I think there is no way to do this with a single SQL statement. I
maybe need to use something like INTERSECTION, but for the moment I'm
seeking information about it, because MS-Access doesn't support this
statement.
If you have a better suggestion, please please reply me!

Thanks for your time!

From: PC Datasheet
Date Posted: 4/19/2004 3:13:00 AM

John,

When you create a query, each column is evaluated as 'And' so with this
in mind,
put CustomerID in the first column, and ProductID in column 2 AND column
3. Set
the criteria for column 2 as 1 and the criteria for column 3 as 2. Your
query
now reads -- 'Return all customers who ordered ProductID 1 AND who
ordered
ProductID 2.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
JohnA wrote:
Dear all,

I'm trying to compose a query, but I don't know how to express the statement.
I have 4 tables: Customers, Orders, Order Details and Products.
Each customer has many orders and each order may include many products.
Let's say that querying Orders and Order Details, we have the following data:
CustomerID ProductID
1 1
1 2
1 3
2 1
2 5
3 1
3 2
4 2
I'd like to display ONLY the customers that ordered BOTH Product 1 AND Product 2!
I don't like to include customers that ordered only one of the 2 products!
The result I'd like to get is:
CustomerID
1
3

Please help to express this sql query...

Thanks in advance,
John


Maybe a double pass query will work.

I'll assume orderdetails table has the order id. Create a Totals query
that groups on orderid. First colum orderid (groupBy). Second column
orderID (Count). Third column is ProductID. In criteria select only
those that match product1 or product2.

Now create a query that you can link the orders table to the customers
table to this query. Drag the count field from the query and enter in
the criteria row the number 2.

Nov 12 '05 #4

P: n/a
John Asproloupos <ja**********@yahoo.com> wrote in
news:40*********************@news.frii.net:
Thanks for your reply!

I've already tried your suggestion, but it doesn't work and I
think that's right.

As you sad, if I set 2 columns of a query as the same field
and then set criteria for both columns, that's evaluated as
AND! So how is it possible a product to be equal to 1 AND 2
simultaneously? It doesn't work this way.
And I think there is no way to do this with a single SQL
statement. I maybe need to use something like INTERSECTION,
but for the moment I'm seeking information about it, because
MS-Access doesn't support this statement.
If you have a better suggestion, please please reply me!

Thanks for your time!
You and Datasheet are on the right track, but have a ways to go.

Open the query builder. Add the orders table TWICE. Yes, two copies
of the table. Now join the tables on customerid.Now put your
customerID and two copies of ProductID in the query grid, making
sure that ONE comes from EACH copy of the table. Add your criteria
and view the results..

Bob Quintal

From: PC Datasheet
Date Posted: 4/19/2004 3:13:00 AM

John,

When you create a query, each column is evaluated as 'And' so
with this in mind,
put CustomerID in the first column, and ProductID in column 2
AND column 3. Set
the criteria for column 2 as 1 and the criteria for column 3
as 2. Your query
now reads -- 'Return all customers who ordered ProductID 1 AND
who ordered
ProductID 2.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 12 '05 #5

P: n/a
You can use the IN Key word with a subquery to do the job of
INTERSECT.

The way it would work would be like as follows:

SELECT CustID FROM tblOrderDetail
WHERE ProdID = 1 AND CustID IN
(SELECT CustID FROM tblOrderDetail WHERE ProdID = 2)

The subquery produces a list of customers who have ordered product 2
(1, 3, 4). The main query then matches items from that list to the
CustIDs who have ordered product 1 (1, 2, 3), to produce the result
(1, 3).

Hope that helps,

-Carlos

John Asproloupos <ja**********@yahoo.com> wrote in message news:<40*********************@news.frii.net>...
Thanks for your reply!

I've already tried your suggestion, but it doesn't work and I think
that's right.

As you sad, if I set 2 columns of a query as the same field and then set
criteria for both columns, that's evaluated as AND! So how is it
possible a product to be equal to 1 AND 2 simultaneously? It doesn't
work this way.
And I think there is no way to do this with a single SQL statement. I
maybe need to use something like INTERSECTION, but for the moment I'm
seeking information about it, because MS-Access doesn't support this
statement.
If you have a better suggestion, please please reply me!

Thanks for your time!

From: PC Datasheet
Date Posted: 4/19/2004 3:13:00 AM

John,

When you create a query, each column is evaluated as 'And' so with this
in mind,
put CustomerID in the first column, and ProductID in column 2 AND column
3. Set
the criteria for column 2 as 1 and the criteria for column 3 as 2. Your
query
now reads -- 'Return all customers who ordered ProductID 1 AND who
ordered
ProductID 2.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.