MSSQL Query Join not Grouping

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.before.pngafter.png

"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.

Hmmm....then you have a few choices- either prepare the statement in whatever UI code and run it the way you want it or better...create a SQL stored procedure where you pass these in as parameters where they will be NULL if "The client might it might not use an item filter. " and build inside the SP the code you want to run

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I forgot to mention and sorry about it...next time - anytime you post online please delete or replace any confidential info like CardNumber...etc. I removed it and would be good if you could do that as well...never post real numbers details for protection please.

Is this what you mean and need? Please note that I moved the
INNER JOIN vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum
right after vbofqMemberAccount which makes sense and should not alter your record set plus now you have the join to the Item table and ItemId = 'XX' right in the INNER JOIN
...
HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID INNER JOIN
HstvbofqCheckItem ON HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID AND HstvbofqCheckItem.ItemID = 'XX'
....

No I dont think that is right. I just need the table HstvbofqCheckItem joined in and then in my where clause I will be passing the ItemID = '' there if needed. If the table HstvbofqCheckItem can be added in and it not "multiply" my count and sum I should be good to go. Am I making any sense at all?

In this example there is one record in the eManager table. In the eLogs table there is one record linked back to eManager. If I leave out the eLogDetails my tSends shows 1 as it should. In eLogDetails there are two records linked back to eLogs. When I join in the eLogDetails table my tSends shows 2 so does tMails.

Is this a little easier to follow the problem? I am trying to get in this example tSends as 1 but tMails as 2.