473,320 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to select only customers that ordered 2 specific products?

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
5 2386
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Andrea | last post by:
Hi, I'm trying to emulate part of our client-server application as a web site so customers can use it, and I'm stuck when it comes to re-ordering items in a list. Basically we have a list of...
0
by: Mike Morton | last post by:
Runnint Mysql 3.23.47 I have the following two tables: backorder_notification ->product_code varchar ->email varchar ->date_added date Products ->code varchar ->name varchar
2
by: Paulo Andre Ortega Ribeiro | last post by:
I have a Microsoft SQL Server 7.0. I wrote a sql command that creates a temporary table with a ORDER BY clause. When a execute a SELECT on this temporary table sometimes the result is ok, but...
2
by: David | last post by:
Hi, I have part of my SQL statement in my asp page as follows: WHERE ((pcbforecast.ShipETA < '31/12/2005') and (products.BBProductName = ....... The problem I am having is that I want the...
4
by: Elroyskimms | last post by:
Using SQL 2000... tblCustomer: CustomerID int CompanyName varchar(20) HasRetailStores bit HasWholesaleStores bit HasOtherStores bit tblInvoiceMessages:
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
1
by: JohnA | last post by:
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...
2
by: kansaskannan | last post by:
I have an Access 2000 database which lists customers by name, and how much (in dollars) they have purchased of various products. How do I write a SQL statement to select customers who make up the...
4
tjc0ol
by: tjc0ol | last post by:
Hi guys, I'm a newbie in php and I got error in my index.php which is: 1054 - Unknown column 'p.products_id' in 'on clause' select p.products_image, pd.products_name, p.products_id,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.