Solved

MSSQL Query Join not Grouping

Posted on 2014-11-11
15
142 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 39

Expert Comment

by:lcohan
Comment Utility
" 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
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
 
LVL 1

Author Comment

by:CLupo
Comment Utility
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
Comment Utility
before.pngafter.png
0
 
LVL 1

Author Comment

by:CLupo
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:CLupo
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
Thank you very much.  This works exactly as I needed.  A+ solution!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now