troubleshooting Question

SQL: "and" criteria across multiple rows

Avatar of Darinwc
Darinwc asked on
Microsoft SQL Server 2008Microsoft SQL ServerDatabases
18 Comments2 Solutions440 ViewsLast Modified:
forgive me because this is very hard to explain.

I am trying to write a query that will find records based on multiple criteria. However the criteria can be spread across multiple rows, rather than multiple columns.

Here is an example:

people table: (personID, personName)
1,joe
2,sally
3,jack

purchase table: (purchaseID, ItemName, personID)
1,apples,3
2,pears,3
3,peanut butter,2
4,pears,2
5,apples,1
6,peanut butter,1
7,jelly,1


OK, so I want to know who bought BOTH peanut butter and jelly.
But if I use
WHERE itemname = "peanut butter" and itemName = "jelly"
then obviously I get nothing.

Please help thank you.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros