Solved

MSSQL Query Join not Grouping

Posted on 2014-11-11
15
165 Views
Last Modified: 2014-11-12
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
0
Comment
Question by:CLupo
  • 8
  • 5
15 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 40435906
" 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'
0
 
LVL 1

Author Comment

by:CLupo
ID: 40436118
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.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40436150
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?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:CLupo
ID: 40436177
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!
0
 
LVL 1

Author Comment

by:CLupo
ID: 40436179
before.pngafter.png
0
 
LVL 1

Author Comment

by:CLupo
ID: 40436182
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

0
 
LVL 40

Expert Comment

by:lcohan
ID: 40436188
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.
0
 
LVL 1

Author Comment

by:CLupo
ID: 40436202
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!
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40436209
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
0
 
LVL 1

Author Comment

by:CLupo
ID: 40436214
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?
0
 
LVL 1

Author Comment

by:CLupo
ID: 40436393
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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40436696
Don't join that table, instead use AND EXISTS ( .... that table where ... ). This will allow you to filter on any of the values held in that table, but it will not multiply the number of rows.

i.e.
WHERE (vbofqMemberAccount.CardNumber LIKE '%29834000015891%')
AND (vboProfile.LastName LIKE '%%')
AND (HstvbofqAssignment.Status = 4)
AND EXISTS (
      SELECT NULL
      FROM HstvbofqCheckItem 
      WHERE HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID
      AND 1 = 1 /* other filtering conditions can be added here */
      )

Open in new window


In full:
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)
AND EXISTS (
      SELECT NULL
      FROM HstvbofqCheckItem 
      WHERE HstvbofqCheck.HstvbofqCheckID = HstvbofqCheckItem.FKHstvbofqCheckID
      AND 1 = 1 /* other filtering conditions can be added here */
      )
GROUP BY vbofqMemberAccount.CardNumber
       , vbofqLastVisit.DateOfBusiness
       , vbogcData.Balance
ORDER BY vbofqMemberAccount.CardNumber
;

Open in new window

0
 
LVL 40

Expert Comment

by:lcohan
ID: 40437518
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
0
 
LVL 1

Author Closing Comment

by:CLupo
ID: 40438173
Thank you very much.  This works exactly as I needed.  A+ solution!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question