Please refer to attached images for better explanation. This is very difficult to articulate in words.
Back story: This query is for a restaurant loyalty system and tracking card members visits/ticket details.
I have the query seen in before.png that is correctly joining all the tables and giving the information needed. There is a count and a sum to show the total number of visits and amount spent. The client wants to be able to limit the data to specific items sold within the tickets. When I add in the table that contains the items sold per ticket it throws the numbers way off because the sum and count are treating the added table as a multiplier for each item sold see after.png. I dont need any results from the added table I just need to include in the where statement a where itemid = 'XX'.
Any help greatly appreciated! Please let me know if you need anymore information.