Help with some SQL
Hi Experts,
I am trying to come up with a Query that will give me which customers have purchased Product 1, but not Product 2.
My Tables and fields are:
Customers
CustomerID
etc.
Purchases
CustomerID
ProductName e.g. Product 1, Product 2 etc.
Notes:
Customers may have purchased same product on different dates
Regards,
Leigh
I am trying to come up with a Query that will give me which customers have purchased Product 1, but not Product 2.
My Tables and fields are:
Customers
CustomerID
etc.
Purchases
CustomerID
ProductName e.g. Product 1, Product 2 etc.
Notes:
Customers may have purchased same product on different dates
Regards,
Leigh
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I ran my first test, I did not have more than one purchase of a given Product for a given Customer.
Here is my new test data - this time I have more than one purchase of a given Product for a given Customer.
Customer
CustomerID Name
1 Customer has Product 1 and Product 2
2 Customer has Product 1, but not Product 2
4 Customer has Product 2, but not Product 1
Purchases
ID CustomerID ProductName
1 1 Product 1
2 1 Product 2
3 2 Product 1
4 4 Product 2
5 1 Product 1
6 1 Product 2
7 2 Product 1
8 4 Product 2
With this data, Duy Pham's query returns the correct customer, but has 2 instances of it:
CustomerID Name
2 Customer has Product 1, but not Product 2
2 Customer has Product 1, but not Product 2
gurpsbassi's query gives the correct answer:
CustomerID Name
2 Customer has Product 1, but not Product 2
Here is my new test data - this time I have more than one purchase of a given Product for a given Customer.
Customer
CustomerID Name
1 Customer has Product 1 and Product 2
2 Customer has Product 1, but not Product 2
4 Customer has Product 2, but not Product 1
Purchases
ID CustomerID ProductName
1 1 Product 1
2 1 Product 2
3 2 Product 1
4 4 Product 2
5 1 Product 1
6 1 Product 2
7 2 Product 1
8 4 Product 2
With this data, Duy Pham's query returns the correct customer, but has 2 instances of it:
CustomerID Name
2 Customer has Product 1, but not Product 2
2 Customer has Product 1, but not Product 2
gurpsbassi's query gives the correct answer:
CustomerID Name
2 Customer has Product 1, but not Product 2
It's the use of distinct.
ASKER
Yes gurpsbassi, adding Distinct to Duy Pham's query fixed it.
Regards,
Leigh
Regards,
Leigh
ASKER
Regards,
Leigh