Making query to find enties of products which have not been ordered.

[cheap]

Gday all!
Newbiee here!

I have a question to all the gurus here about an sql query I'm tryin
to make.
Anyway here goes:

I have 4 tables.

Table 1: Customer
- Customer_ID
- Name

Table 2: Sales
- Order_ID
- Customer_ID
- Order_Date

Table 3: Order
- Order_ID
- Product_ID
- Quantity

Table 4: Product
- Product_ID
- Name

I think the way the tables are linked is obvious so I don't need t
explain.


I want to make a report which runs a query to list all the product
that each customer have not ordered before.

e.g.

There are 3 customer Abe, Ben, Charlie.
There are 3 products Apple, Ball, Car.

Abe ordered Apple and Car before.
Ben ordered Apple.
Charlie did not order anything.

So I want the results of my report to be.

Abe: Ball.
Ben: Ball, Car.
Charlie: Apple, Ball, Car.

--------------------

Was I clear enough?
So any of you have any ideas?

Currently what I did was list out all the products that they ordere
previously and manually check the products which they have not ordere
by refering to that lists.

Helps appreciated.

Thanks in advance for helping out this newbiee

 

[Allen Browne]

1. Create a query using just the Customer and Product tables.
The will be no join between the 2 tables in the upper pane of query design.
As a result, you get every possible combination of Customer and Product in
the query output. Save.

2. Use the Unmatched query Wizard (last choice in the first dialog when you
create a new query) to find the records in Query1 that are not in your Order
table.

 

[Allen Browne]

BTW, there are also issues here with the table/field names.

ORDER is a reserved word, and Name is ambigous in a form or report (since
the form/report has a Name.)

 

[cheap]

Thanks Allen!!! I will try your solutions tomorow and see how it goes.

as for the "Name" being in 2 different tables it's my mistake fo
writing it here to make things simpler...... the real tables and field
have different names.

Once again. Thanks

 

[cheap]

1. Create a query using just the Customer and Product tables.
The will be no join between the 2 tables in the upper pane of quer
design.
As a result, you get every possible combination of Customer and Produc
in
the query output. Save.

2. Use the Unmatched query Wizard (last choice in the first dialog whe
you
create a new query) to find the records in Query1 that are not in you
Order
table.

Allen,
I've tried doing what you said but what I get was the list of al
customers with the products that EVERYBODY has not ordered.

e.g.

There are 3 customer Abe, Ben, Charlie.
There are 3 products Apple, Ball, Car.

Abe ordered Apple and Ball before.
Ben ordered Apple.
Charlie did not order anything.

What I get right now is:
Abe: Car.
Ben: Car.
Charlie: Car.

What I want is:
Abe: Car.
Ben: Ball, Car.
Charlie: Apple, Ball, Car.

What am I doing wrong?
Helppppp

Thanks in Advance!!

 

[Allen Browne]

The unmatched query needs to join on both fields, i.e. on the Customer_ID
and Product_ID.

Query1:
SELECT Customer_ID, Product_ID FROM Customer, Product;

Query2:
SELECT Sales.Customer_ID, [Order].ProductID
FROM Sales INNER JOIN Order ON Sales.Order_ID = [Order].Order_ID;

Query3:
SELECT Query1.*
FROM Query1 INNER JOIN Query2
ON Query1.Customer_ID = Query2.Customer_ID
AND Query1.Product_ID = Query2.Product_ID
WHERE Query2.Customer_ID Is Null;

 

[cheap]

Allen,
I've copyed your code and it's returning me no results?
I've also tryed joining just 1 of the tables.... the results not what
wanted.

Any other suggestions

 

[Allen Browne]

There's more than just copying and pasting here. You will need to figure out
what's what so you can use your own field names, table names, and get the
joins right.

The essential techniques are:
- the Cartesian Product (Query1) to generate all combinations;
- the outer join, so the nulls are included;
- the frustrated join (criteria to eliminate the values that do match.)