Avatar of CLupo
CLupo
 asked on

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.png
after.png
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
CLupo

8/22/2022 - Mon
lcohan

" I dont need any results from the added table I just need to include in the where statement a where itemid = 'XX'."

if the query you have gives you the correct results just put it between brackets and alias it to make it a sub-query then just SELECT from it where itemid = 'XX'.

should be something like:

SELECT * FROM
(
...your query here
)  Q
where q.itemid = 'XX'
CLupo

ASKER
I don't always have an item I want to filter on. The client might it might not use an item filter.

To clarify the before works correctly without the items.
lcohan

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


http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures

Would it be possible to post the query in plain text instead of the PNG?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
CLupo

ASKER
Here is the query...

SELECT     vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, SUM(HstvbofqCheck.CheckTotal) AS tSpent, COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits, 
                      vbogcData.Balance
FROM         vbofqMemberAccount INNER JOIN
                      vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN
                      vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN
                      vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN
                      vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN
                      HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN
                      HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID INNER JOIN
                      HstvbofqCheckItem ON HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID LEFT OUTER JOIN
                      vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum
WHERE     (vbofqMemberAccount.CardNumber LIKE '%29834000015891%') AND (vboProfile.LastName LIKE '%%') AND (HstvbofqAssignment.Status = 4)
GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance
ORDER BY vbofqMemberAccount.CardNumber

Open in new window

Also I am completely self taught in SQL so all this is very new to me.  Thanks for the help!
CLupo

ASKER
before.pngafter.png
CLupo

ASKER
That previous post of the Query is the not working correctly one that I need help with.  Here is the query working correctly without the ITEMS portion added in...

SELECT     vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, SUM(HstvbofqCheck.CheckTotal) AS tSpent, COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits, 
                      vbogcData.Balance
FROM         vbofqMemberAccount INNER JOIN
                      vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN
                      vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN
                      vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN
                      vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN
                      HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN
                      HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID LEFT OUTER JOIN
                      vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum
WHERE     (vbofqMemberAccount.CardNumber LIKE '%29834000015891%') AND (vboProfile.LastName LIKE '%%') AND (HstvbofqAssignment.Status = 4)
GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance
ORDER BY vbofqMemberAccount.CardNumber

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
lcohan

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

ASKER
This is not live data and these numbers do not reflect the live working numbers but mental note has been made.   Thanks for your continued efforts in helping me with this!
lcohan

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


So overall the code should look like:


SELECT      vbofqMemberAccount.CardNumber,
            vbofqLastVisit.DateOfBusiness,
            SUM(HstvbofqCheck.CheckTotal) AS tSpent,
            COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits,
            vbogcData.Balance
 FROM vbofqMemberAccount INNER JOIN
        vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum INNER JOIN
      vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN
      vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN
      vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN
      vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN
      HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN
      HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID INNER JOIN
      HstvbofqCheckItem ON HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID AND HstvbofqCheckItem.ItemID = 'XX'
 WHERE (vbofqMemberAccount.CardNumber LIKE '%xxxxxxxx%') AND (vboProfile.LastName LIKE '%%') AND (HstvbofqAssignment.Status = 4)
 GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance
 ORDER BY vbofqMemberAccount.CardNumber
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
CLupo

ASKER
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?
CLupo

ASKER
Here is a more stripped down example of the same "problem"

SELECT     eManager.eTitle, eManager.eFrom, eManager.eSubject, COUNT(eLogs.eLogID) AS tSends, COUNT(eLogDetails.eLogDetailID) AS tMails
FROM         eManager INNER JOIN
                      eLogs ON eManager.eMgrID = eLogs.eMgrID INNER JOIN
                      eLogDetails ON eLogs.eLogID = eLogDetails.eLogID
GROUP BY eManager.eTitle, eManager.eFrom, eManager.eSubject

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.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
lcohan

CREATE PROCEDURE [GetSalesOrders] (@ItemID SYSNAME)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(4000);

IF @ItemID IS NOT NULL
      SET @SQL = N'
      SELECT * FROM
      (
      SELECT     vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, SUM(HstvbofqCheck.CheckTotal) AS tSpent, COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits,
                                      vbogcData.Balance,
                                      HstvbofqCheck.HstvbofqCheckID
      FROM         vbofqMemberAccount INNER JOIN
                                      vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN
                                      vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN
                                      vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN
                                      vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN
                                      HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN
                                      HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID LEFT OUTER JOIN
                                      vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum
      WHERE     (vbofqMemberAccount.CardNumber LIKE ''%29834000015891%'') AND (vboProfile.LastName LIKE ''%%'') AND (HstvbofqAssignment.Status = 4)
      GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance
      ) Q WHERE EXISTS
                        (SELECT ItemId FROM HstvbofqCheckItem
                                WHERE HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID
                                          AND HstvbofqCheckItem.ItemId = '+@ItemId+'
      ORDER BY vbofqMemberAccount.CardNumber';

--if NO value or NULL value is passed in then run the original select
IF @ItemID IS NULL
      SET @SQL = N'
      SELECT     vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, SUM(HstvbofqCheck.CheckTotal) AS tSpent, COUNT(HstvbofqAssignment.HstvbofqAssignmentID) AS tVisits,
                                      vbogcData.Balance,
                                      HstvbofqCheck.HstvbofqCheckID
      FROM         vbofqMemberAccount INNER JOIN
                                      vboProfile ON vbofqMemberAccount.FKvboProfileID = vboProfile.vboProfileID INNER JOIN
                                      vboProfileContact ON vboProfile.vboProfileID = vboProfileContact.FKvboProfileID INNER JOIN
                                      vboProfileLocation ON vboProfile.vboProfileID = vboProfileLocation.FKvboProfileID INNER JOIN
                                      vbofqLastVisit ON vbofqMemberAccount.vbofqMemberAccountID = vbofqLastVisit.FKvbofqMemberAccountID INNER JOIN
                                      HstvbofqAssignment ON vbofqMemberAccount.vbofqMemberAccountID = HstvbofqAssignment.FKvbofqMemberAccountID INNER JOIN
                                      HstvbofqCheck ON HstvbofqAssignment.HstvbofqAssignmentID = HstvbofqCheck.FKHstvbofqAssignmentID LEFT OUTER JOIN
                                      vbogcData ON vbofqMemberAccount.CardNumber = vbogcData.CardNum
      WHERE     (vbofqMemberAccount.CardNumber LIKE ''%29834000015891%'') AND (vboProfile.LastName LIKE ''%%'') AND (HstvbofqAssignment.Status = 4)
      GROUP BY vbofqMemberAccount.CardNumber, vbofqLastVisit.DateOfBusiness, vbogcData.Balance
      ORDER BY vbofqMemberAccount.CardNumber';

EXEC sp_executeSQL @SQL

GO

--to test both cases please execute the stored proc with NULL for no value or some ItemId
EXEC [GetSalesOrders] 112345
--OR like below if ItemId is a string
EXEC [GetSalesOrders] 'A1B2C3D4'

--then no ItemId
EXEC [GetSalesOrders] NULL
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CLupo

ASKER
Thank you very much.  This works exactly as I needed.  A+ solution!