Using a subquery in Crystal Reports to filter transactions - Similar to using SQL's IN() or NOT IN()

In my restaurant, I need to measure the ability of my waiter staff to sell alcoholic drinks to guests who are having dinner.  I only want to evaluate transactions (tickets / receipts) where the transaction contains a qualifying item. For example, I don't care to look at a party's transaction if they ordered drinks without an entree. I only want to see the add-on items if the dining party had lobster or a steak. E.g., "For each transaction that includes lobster or steak, count the number of alcoholic drinks that were added." It doesn't matter if it was a party of 1 of a party of 10. If any one guest had a steak or a lobster, count the quantity of drinks on that transaction / receipt / ticket. There is a transaction table, which holds the time & date, the cashier id, the register id, and so forth. There is a transaction-entry table that contains the line items (e.g. steak). Each transaction entry (aka line item) belongs to one transaction. A transaction can have one item or many items.

I have added the transaction & transaction-entry tables twice, as was suggested in http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_22835462.html.  However, I am getting duplicate rows if the party had 2 or 3 steaks on their transaction. Is there a trick to the join? This feels like a boolean 'OR', but I'm not sure how to implement it. Could the 'Enforce Join' options help?

In SQL it would be something like......

SELECT count(*) FROM transactionEntry TRE
LEFT OUTER JOIN transaction TRX
ON TRE.transactionId = TRX.id
WHERE TRX.id
IN
(
SELECT transactionEntry TRE2
LEFT OUTER JOIN transaction TRX2
ON TRE2.transactionId = TRX2.id
WHERE TRE.item IN ('steak', 'lobster')
)
GROUP BY TRX.id
oakie22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kenwagersCommented:
The best way I've found to do this is take the SQL you've created above that provides exactly what you need, and then add it as a command object, and run the report off that object.  You can avoid adding any individual tables at all.

The command object will take any valid SQL statement, and allows you to create parameters that you can pass into the SQL at the time the report is run.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
I would try using a command object because Crystal won't do it through adding the tables.  You can't use the type of filter you want based on tables only through the command object

Create a new report
Set the database but instead of adding tables use the ADD COOMAND object then use the SQL as you have above.

mlmcc
0
MIKESoftware Solutions ConsultantCommented:
You can also create a SQL Expression to accomplish this as a unique identifer in your data that is linked to an ID. The SQL expression  is used as a field and it should return a INTIGER as and ID field.

IT is very efficient and helpful in these types of situations.
0
oakie22Author Commented:
I haven't fully implemented this solution. Having just started, it looks correct & promising.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Server Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.