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
SELECT transactionEntry TRE2
LEFT OUTER JOIN transaction TRX2
ON TRE2.transactionId = TRX2.id
WHERE TRE.item IN ('steak', 'lobster')
GROUP BY TRX.id