Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecting records - multiple differing items with another common item present

Status
Not open for further replies.

JD5241

Technical User
Jan 17, 2005
154
US
Crystal XI
SQL 2008 db

I have a customer order table, multiple lines per order (they share the same order number, which the report is sorted by). I'm trying to figure out a way to filter the data by item number twice, I will attempt to explain it clearly.
I have a list of several items that are on some orders. I need to pull those orders only if a different item also exists on those orders.
Example:
Item numbers
111
112
113
114
115

I need to pull the orders for those item numbers only if item 'AAA' is also on each of those orders.
I can't just put all the item numbers into a formula filter or the Select Expert, as it will pull all orders with 'AAA', when I need it limited to just those orders that also have the short list of items I have.
I've looked over this site, every other Crystal resource I could find, and I'm stumped. Any help is appreciated. Thanks!
 
I will attempt to explain it clearly."

What happens in vagueness stays in vagueness!

Your example bares no resemblance to your explanation. There are absolutely no AAAs in the former!

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Let me try again... say I have 4 orders, keeping in mind the list of items in my first post:
Order 1 contains items:
111
AAA
Order 2 contains items:
112
555
Order 3 contains items:
114
AAA
Order 4 contains items:
444
AAA

I only want to pull the orders that have the items in my list and the singular item 'AAA', so I only want to see orders 1 and 3.
All of these items may appear in other orders on their own, but I only want to see the ones where item 'AAA' is also in the order. So the returned data has to be a combination of this list of items, and the singular 'AAA' item only.
 
Need an example of the table with representative data.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top